MySQL query for selecting distinct rows with all possible values in a column

Here’s my DB table named ‘test_tbl’:

id	type
1	A
1	B
1	C
1	D
2	A
2	B
2	C
2	D
3	A
3	B
4	A
4	D

Here every ‘id’ can have at most 4 possible values (A,B,C,D) for ‘type’ column. I want to find out those ids who don’t have all four values in ‘type’ column. So my expected output should be ids (3,4). I have tried as following:

select DISTINCT id
from test_tbl
where id NOT IN 
    (SELECT id FROM test_tbl 
    where
    type='A' and type='B' and type='C' and type='D');

But this is giving output all the ids from table.

Answer

Use aggregation:

select id
from test_tbl
group by id
having count(distinct type) <> 4;

If you can have types other than A, B, C, and D, then add:

where type in ('A', 'B', 'C', 'D')

Advertisement

Answer