CODEWITHSUNDEEP
mysqlsqljoin
n34_panda
n34_panda

Reputation: 2677

With SQL how to select rows that the joined table matches 2 values

If we have a table, orders:

Order ID Order Table
1 100
1 50

And we have a table, OrderProducts:

OrderID Product ID ProductName ProductType
1 1 ProductOne Small
1 2 ProductTwo Big
1 3 ProductThree Small
2 4 ProductFour Big
2 5 ProductFive Big

How do I use SQL to return only the Orders that contain products of 2 specific types. In the scenario above I want to return only products with ProductType = 'Small' and 'Big' but ignore orders like "2" which only have products of two 'Big' on.

Results:

Order ID
1

I have tried creating a view to help, inner joins with multiple clauses but I am struggling. I am using MySQL for this and think my logic is simply "out".

Any advice is appreciated.

Upvotes: 1

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Aggregation is one method:

select order_id
from orderproducts op
where ProductType in ('Small', 'Big')
group by order_id
having count(distinct ProductType) = 2;

Upvotes: 2

Related Questions