Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE things (
  id      int PRIMARY KEY
, key     int NOT NULL
, part    text
, details text
);

INSERT INTO things (id, KEY, part, details) VALUES
  ( 1, 1, 'a', '1a details')
, ( 2, 1, 'b', '1b details')
, ( 3, 1, 'c', '1c details')
, ( 4, 2, 'a', '2a details')
, ( 5, 2, 'b', '2b details')
, ( 6, 2, 'c', '2c details')
, ( 7, 3, 'a', '3a details')
, ( 8, 3, 'c', '3c details')
, ( 9, 4, 'b', '4b details')
, (10, 5, 'b', '5b details')
, (11, 6, 'b', '6b details')
, (12, 6, 'c', '6c details')
, (13, 7, 'a', '7a details')
, (14, 8, 'a', '8a details')
;

CREATE INDEX on things (key, part); -- unless there is a UNIQUE constraint???
14 rows affected

SELECT t1.key
FROM   things t1
JOIN   things t2 USING (key)
JOIN   things t3 USING (key)
WHERE  t1.part = 'a'
AND    t2.part = 'b'
AND    t3.part = 'c';


key


1

2


-- your attempt
select * 
from things t
where t.key in (
  select x.key
  from things x
  group by x.key
  having count(distinct part) = 3
);

-- having distinct part = ['a', 'b', 'c']


id
key
part
details


1
1
a
1a details

2
1
b
1b details

3
1
c
1c details

4
2
a
2a details

5
2
b
2b details

6
2
c
2c details

id	key	part	details
1	1	a	1a details
2	1	b	1b details
3	1	c	1c details
4	2	a	2a details
5	2	b	2b details
6	2	c	2c details