MySQL : extract rows having 2 different values in a column-CodePudding

I have a table with 3 columns looking like this :

id	key	status
1	1	a
2	2	a
3	3	a
4	1	b
5	2	b
6	4	b

I want to extract rows where a key have a "a" AND a "b" as status...

id	key	status
1	1	a
2	2	a
4	1	b
5	2	b

I KNOW that I need some GROUP BY and HAVING but I twisted my brain for few hours and I don't find any solution...

Thanx !

CodePudding user response：

You can use having count

select `key`
from my_table
where status in ('a','b')
group by `key`
having count(distinct status) =2;

If you need the other values you can use inner join with subquery

select m.*
from my_table m
inner join (select `key`
            from my_table
            where status in ('a','b')
            group by `key`
           having count(distinct status) =2
           ) tbl on m.`key`=tbl.`key`;

https://dbfiddle.uk/yq5OiPtU