mysqlsqlselectmysql-select-db

Special Select Statement. Select all SKU base on its Main SKU


Every one. I have a mySQL table like this:

DATA(sku,quantity)
ABC           55
ABC_DE005     1
ABC_DE006     1
ABC_DE007     1
ABC_DE008     1
DEF           30
DEF_DE56      1
DEF_DE57      1
DEF_DE58      1
DEF_DE59      1
DEF_DE60      1
DEF_DE61      1
XYZ           0
XYZ_DE565     0
XYZ_DE566     0
XYZ_DE567     0
XYZ_DE568     0
XYZ_DE569     0

ABC is the main SKU, ABC_DExx is the sub SKU and the quantity always is 1 or 0.
My question is:
How can I select all the Sku which have quantity over 20 base on the main SKU? Or If main SKU have quantity over 20 then main SKU and it sub SKU will be selected.
some thing like

     "SELECT * From DATA Where quantity > 20 ..." 

Below is the final result I want (main SKU (Ex: ABC) and it sub SKU (ex: ABC_DExx) will show up because main SKU ABC is over 20):

DATA(sku,quantity)
ABC           55
ABC_DE005     1
ABC_DE006     1
ABC_DE007     1
ABC_DE008     1
DEF           30
DEF_DE56      1
DEF_DE57      1
DEF_DE58      1
DEF_DE59      1
DEF_DE60      1
DEF_DE61      1

Thanks


Solution

  • I tried using CONCAT Operator; please find the below code.

    Demo

    SELECT DATA.sku, DATA.quantity FROM DATA
    INNER JOIN (SELECT * FROM DATA WHERE quantity > 20) DATA1 ON
    DATA.sku = DATA1.sku OR DATA.sku LIKE CONCAT(DATA1.sku, '%');
    

    Output

    Output of above query