SQL SSMS Select clause with 2 where functions for same field-CodePudding

I want to find customers who have purchased both a piece of fruit and a piece of chocolate in the same 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
30761      2019-05-03       Chocolate
53899      2019-05-03       Fruit

I want this:

  ID         purchase_date
  111999     2021-06-03

I have tried a select function with a where clause, but It isn't returning any record. Any ideas?

SELECT ID, purchase_date
From dbo.mytable
where product='Fruit' AND product='Chocolate'
Group by ID, purchase_date

EDIT- does anyone know how I might find people who only bought fruit in a transaction?

CodePudding user response：

If I understand correctly, you can try to write aggregate condition function in HAVING

SELECT ID,purchase_date    
FROM dbo.mytable
GROUP BY  ID,purchase_date    
HAVING 
    COUNT(CASE WHEN product='Fruit' THEN 1 END) > 0 
AND
    COUNT(CASE WHEN product='Chocolate' THEN 1 END) > 0

CodePudding user response：

One method would be to use a conditional COUNT in the HAVING:

SELECT ID,
       Purchase_date
FROM dbo.MyTable
GROUP BY ID,
         Purchase_date
HAVING COUNT(DISTINCT CASE WHEN product IN ('Fruit','Chocolate') THEN product END) = 2;

CodePudding user response：

And yet another option since many people seem to ignore or forget about set operators.

select ID, purchase_date from dbo.mytable where product='Fruit' 
intersect
select ID, purchase_date from dbo.mytable where product='Chocolate';

Notice the lack of an ORDER BY clause. If order matters (and usually it does), then you must include an ORDER BY clause.

CodePudding user response：

your data

declare @a TABLE (
   ID            int  NOT NULL 
  ,purchase_date DATE  NOT NULL
  ,product       VARCHAR(9) NOT NULL
);
INSERT INTO @a(ID,purchase_date,product) VALUES (22212,'2021-06-03','Fruit');
INSERT INTO @a(ID,purchase_date,product) VALUES (111999,'2021-06-03','Fruit');
INSERT INTO @a(ID,purchase_date,product) VALUES (111999,'2021-06-03','Chocolate');
INSERT INTO @a(ID,purchase_date,product) VALUES (56727,'2019-05-03','Bread');
INSERT INTO @a(ID,purchase_date,product) VALUES (30726,'2019-05-03','Fruit');
INSERT INTO @a(ID,purchase_date,product) VALUES (30761,'2019-05-03','Chocolate');
INSERT INTO @a(ID,purchase_date,product) VALUES (53899,'2019-05-03','Fruit');

your final query

with t as(
select ID, purchase_date,STRING_AGG( product,',') products  from @a
group by ID,purchase_date)

select ID,purchase_date from t
where products like N'%Chocolate%' and  products like N'%Fruit%'