Unsure how to use where clause with two columns-CodePudding

I just can’t figure this one out. I've been trying for hours. I have a table like this…

ID	sample
1	A
1	B
1	C
1	D
2	A
2	B
3	A
4	A
4	B
4	C
5	B

I'm interested in getting all the samples that match 'A', 'B' and 'C' for a given ID. The ID must contain all 3 sample types. There are a lot more sample types in the table but I'm interested in just A, B and C.

Here's my desired output...

ID	sample
1	A
1	B
1	C
4	A
4	B
4	C

If I use this:

WHERE sample in ('A', 'B', 'C')

I get this result:

ID	sample
1	A
1	B
1	C
1	D
2	A
2	B
3	A
4	A
4	B
4	C
5	B

Any ideas on how I can get my desired output?

CodePudding user response：

One ANSI compliant way would be to aggregate using a distinct count

select id, sample
from t
where sample in ('A','B','C') 
and id in (
  select id from t 
    where sample in ('A','B','C') 
    group by id 
    having Count(distinct sample)=3
);

CodePudding user response：

WHERE sample in (‘A’, ‘B’, ‘C’)

Should eliminate any other samples such as 'D'.

You could also try the following:

WHERE sample = ('A' OR 'B' OR 'C')

CodePudding user response：

Not sure what flavor of SQL is being used, but here's an example to work off of:

Postgre - db-fiddle

SELECT id 
FROM t
GROUP BY id
HAVING array_agg(sample) @> array['A', 'B', 'C']::varchar[];
-- HAVING 'A' = ANY (array_agg(sample))
--    AND 'B' = ANY (array_agg(sample))
--    AND 'C' = ANY (array_agg(sample))

Presto

SELECT id 
FROM t
GROUP BY id
HAVING contains(array_agg(sample), 'A')
   AND contains(array_agg(sample), 'B')
   AND contains(array_agg(sample), 'C')