SQL Use HAVING to check for multiple conditions in a group


Example

Orders Table

CustomerIdProductIdQuantityPrice
125100
132200
141500
21450
356700

To check for customers who have ordered both - ProductID 2 and 3, HAVING can be used

 select customerId
 from orders
 where productID in (2,3)
 group by customerId
 having count(distinct productID) = 2

Return value:

customerId
1

The query selects only records with the productIDs in questions and with the HAVING clause checks for groups having 2 productIds and not just one.

Another possibility would be

 select customerId
 from orders
 group by customerId
 having sum(case when productID = 2 then 1 else 0 end) > 0
    and sum(case when productID = 3 then 1 else 0 end) > 0

This query selects only groups having at least one record with productID 2 and at least one with productID 3.