How to select rows that have certain values present in another column-CodePudding

I have a table :

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

my goal is to have the table where I have only IDs that have A,B,C present per id,

in this case it is:

id
1

how to construct the SQL query for that ?

CodePudding user response：

One canonical approach uses aggregation:

SELECT id
FROM yourTable
WHERE value IN ('A', 'B', 'C')
GROUP BY id
HAVING COUNT(DISTINCT value) = 3;

CodePudding user response：

To use exists statement like this:

select id from ${table} a where value = 'A' 
and exists (select 1 from ${table} b where a.id = b.id and b.value = 'B')
and exists (select 1 from ${table} c where a.id = c.id and b.value = 'C')

To create index on column id will be more nice.