Only select one row with matches list column value

Question

I have a table structure

colA | colB
--------------
1    | A
1    | B
1    | C
2    | X
2    | B
2    | C
3    | Y
3    | B
3    | X

How can I get colA return = 2 with condition WHERE colB are X, B, C ? I tried to use this SELECT colA FROM table WHERE colB IN ('X','B','C'), but the result is 1,1,2,2,2,3,3

Answer 1

Consider using aggregation:

select a
from mytable 
group by a
having
    sum(b = 'B') >= 1
    and sum(b = 'C') >= 1
    and sum(b = 'X') >= 1

This will give you values of column a for each at least a record exists with column b having value 'B' and one for value 'C' and one for value 'X'.

You can make this more restrictive, say if you want no other possible value in b than 'A', 'B', 'X':

select a
from mytable 
group by a
having
    sum(b = 'B') = 1
    and sum(b = 'C') = 1
    and sum(b = 'X') = 1
    and count(*) = 3