SSMS SQL Identify purchases who only buy one specific item

I want to find customers who have only bought fruit in their purchase. My data looks like this:

ID         purchase_date    product
22212      2021-06-03       Fruit
111999     2021-06-03       Fruit
111999     2021-06-03       Chocolate
56727      2019-05-03       Bread
30726      2019-05-03       Fruit
30726      2019-05-03       Chocolate
53899      2019-05-03       Fruit

I want this:

  ID         purchase_date
  22212      2021-06-03
  53899      2019-05-03   

Thinking I need a where clause, grouped by ID and Purchase_date?

Answer

You can try to use Having with condition aggregate function to write the logic.

• Buy fruit (COUNT with fruit count will be greater than 1 )
• Din’t buy any product without Fruit

Query 1:

SELECT ID,purchase_date    
FROM T
GROUP BY  ID,purchase_date    
HAVING 
    COUNT(CASE WHEN product='Fruit' THEN 1 END) > 0 
AND
    COUNT(CASE WHEN product<>'Fruit' THEN 1 END) = 0 

Results:

|    ID | purchase_date |
|-------|---------------|
| 53899 |    2019-05-03 |
| 22212 |    2021-06-03 |