Sql-server – Select results from table ONLY if rows meet 2 conditions

relational-divisionsql server

**OrdersTable**
  OrderID
  Product

SELECT OrderId, Product
FROM OrdersTable
WHERE Product = 'ProductA'
AND Product = 'ProductB' --doesn't work as AND looks for the same data in one row

OrderID   Product
-----------------
628       ProductB
628       ProductA

I am trying to query OrderID's that contain BOTH Product 'A' AND Product 'B'.
I've tried using EXISTS, AND/OR, etc but they always return an either or situation (i.e. the query returns orders that have a Product 'A', but not a Product 'B' or vice versa). How can I query only OrderID's that contain both Products A and B?

Best Answer

You can use an aggregate for that:

SELECT OrderId
FROM OrdersTable
WHERE Product in ('ProductA', 'ProductB')
group by orderid
having count(distinct Product) = 2;

This will give you those orders that include at least those two products. The orders might include other products as well.

Related Question