Garrett C. Garrett C. - 17 days ago 5
MySQL Question

SQL - Select All rows with Value X when a Row has Value Y

I am trying to get data from a row, then look up all rows with the data from that row.

My Table is like this:

Invoice Desc Item_Code Units Price Amount
1000 PHASE A 45 10 20 200
1000 BOLTS 16 45 1 45
1000 Jerry 10 1 100 100
1001 PHASE B 19 10 5 50


I want to return all rows with whatever is in Invoice when Desc is LIKE PHASE A.

I know enough to write:

SELECT
Invoice, Desc, Item_Code, Units, Price, Amount
FROM tbl
WHERE
Desc LIKE '%PHASE A%'


This will only return the row containing PHASE A - I want every row of PHASE A as below:

1000 PHASE A 45 10 20 200
1000 BOLTS 16 45 1 45
1000 Jerry 10 1 100 100


I feel like that should be in a IN statement, or I need to append an IN statement, but don't know where it should go.

Answer

I feel like that should be in a IN statement, or I need to append an IN statement, but don't know where it should go.

Here

SELECT Invoice,
       Desc,
       Item_Code,
       Units,
       Price,
       Amount
FROM   tbl
WHERE  Invoice IN (SELECT Invoice
                   FROM   tbl
                   WHERE  Desc LIKE '%PHASE A%') 
Comments