SQL GROUP BY where partitions meet condition

Consider following table:

id     var_a     var_b
----------------------
0      abcd      123
1      abcd      245
2      efgh      123
3      abcd      456
4      efgh      211
5      ijkl      123
6      ijkl      456

I want to group by var_a in such a way, that each group would have in it var_b = ‘123’ and var_b = ‘456’. That is, I want to obtain “abcd” and “ijkl” in the set of results. How do I do that?

Answer

You can filter the table and group by var_a:

select var_a
from tablename
where var_b in ('123', '456')
group by var_a
having count(distinct var_b) = 2

The condition in the having clause makes sure that both '123' and '456' exist for the same var_a.