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?

CodePudding user response：

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 |

CodePudding user response：

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

CodePudding user response：

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 
                   )