How to use WHERE query at same column in same table?

Question

I have this data.

tbl_data

id    value
1       A
1       B
1       C
2       A
2       C

I want to select id which have 'A' and 'B' as value.

SELECT id FROM tbl_data WHERE value = 'A' AND value = 'B'

But it returns zero result.

How to make it to return id 1 ?

Answer 1

select id from table 
where
value  in ('A', 'B')
group by id 
having count( distinct value ) = 2