SQL Filter results using WHERE and HAVING Use HAVING to check for multiple conditions in a group
Example
Orders Table
| CustomerId | ProductId | Quantity | Price |
|---|---|---|---|
| 1 | 2 | 5 | 100 |
| 1 | 3 | 2 | 200 |
| 1 | 4 | 1 | 500 |
| 2 | 1 | 4 | 50 |
| 3 | 5 | 6 | 700 |
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.
