sql - How to select two products where it is in same OrderDetails

Question

How do I select an order with two products in the OrderDetails?

For example, if I have 4 orders:

order id: 11000 contains: p1, p3, p5, p9
order id: 12000 contains: p1, p4, p5, p8
order id: 13000 contains: p2, p3, p5, p7
order id: 14000 contains: p1, p3, p5, p8
order id: 15000 contains: p2, p3, p6, p9

I want to select order ids where they contain p1 and p9

The expected result: 11000, 12000, 14000

How do I do this in SQL Server?

Answer 1

You can use something like:

SELECT id
FROM OrderDetails
WHERE Product IN ('p1','p9')
GROUP BY id
HAVING COUNT(DISTINCT Product) = 2

to get the OrderDetails PK value that satisfies the condition set.

Edit:

To get the count of the PK values, you can wrap the above query in a subquery and COUNT:

SELECT COUNT(*)
FROM (
  SELECT id
  FROM OrderDetails
  WHERE Product IN ('p1','p9')
  GROUP BY id
  HAVING COUNT(DISTINCT Product) = 2) AS t