'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?

sqlsql-servergroup-by


Solution 1:[1]

Select Id, purchase_date 
From your_table_name 
Where lower(product) = 'fruit'

Solution 2:[2]

You can use correlated queries as well.

SELECT DISTINCT T1.ID, T1.purchase_date
FROM <TableName> T1
WHERE T1.product = 'Fruit'
    AND NOT EXISTS (    SELECT 1 
                        FROM <TableName> T2 
                        WHERE Product <> 'Fruit' 
                        AND T1.ID = T2.ID 
                   )

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 marc_s
Solution 2 Arpit Chinmay

Related Questions