CODEWITHSUNDEEP
mysql
bcsteeve
bcsteeve

Reputation: 1001

mysql select to give all rows of one column that match any row of another?

+-----------------+-----------+-------+-------------+
| orders_total_id | orders_id | value |    class    |
+-----------------+-----------+-------+-------------+
|               1 |         1 | 34.00 | ot_subtotal |
|               2 |         1 | 8.56  | ot_shipping |
|               3 |         1 | 2.38  | ot_tax      |
|               4 |         1 | 0.600 | ot_tax      |
|               5 |         2 | 45.54 | ot_subtotal |
|               6 |         2 | 8.56  | ot_shipping |
+-----------------+-----------+-------+-------------+

I want to show ALL the records that belong to ANY rows which also have a row that has class=ot_tax. So in the above, rows 1-4 would match because one (or more) of them have a class of ot_tax and they all have the same orders_id, but NOT rows 5-6 because they also have same orders_id but none have class=ot_tax.

I want to say the key here has to do with grouping, but SQL syntax still feels so foreign to me. Thanks.

Upvotes: 0

Views: 101

Answers (1)

jpw
jpw

Reputation: 44881

So you want to get all orders that have a row with class='ot_tax' ?

There are several ways, for instance using the in predicate:

SELECT * FROM table_name 
WHERE orders_id IN (SELECT orders_id FROM table_name WHERE class='ot_tax')

Upvotes: 1

Related Questions