By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE a (id int, b_id int)
CREATE TABLE b (id int)
INSERT INTO a VALUES (1, 1), (1, 2), (1, 3), (2, 2), (3, 2), (3, 3)
6 rows affected
WITH b(id) AS ( -- emulating empty set
SELECT 0
WHERE 1=2
)
SELECT a.id
FROM a
LEFT JOIN b on a.b_id = b.id
GROUP BY a.id
HAVING COUNT(b.id) = (SELECT COUNT(*) FROM b);
| id |
|---|
| 1 |
| 2 |
| 3 |
WITH b(id) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3
)
SELECT a.id
FROM a
LEFT JOIN b on a.b_id = b.id
GROUP BY a.id
HAVING COUNT(b.id) = (SELECT COUNT(*) FROM b);
| id |
|---|
| 1 |
WITH b(id) AS (
SELECT 1 UNION SELECT 2
)
SELECT a.id
FROM a
LEFT JOIN b on a.b_id = b.id
GROUP BY a.id
HAVING COUNT(b.id) = (SELECT COUNT(*) FROM b);
| id |
|---|
| 1 |
WITH b(id) AS (
SELECT 2
)
SELECT a.id
FROM a
LEFT JOIN b on a.b_id = b.id
GROUP BY a.id
HAVING COUNT(b.id) = (SELECT COUNT(*) FROM b);
| id |
|---|
| 1 |
| 2 |
| 3 |
WITH b(id) AS (
SELECT 2 UNION SELECT 3
)
SELECT a.id
FROM a
LEFT JOIN b on a.b_id = b.id
GROUP BY a.id
HAVING COUNT(b.id) = (SELECT COUNT(*) FROM b);
| id |
|---|
| 1 |
| 3 |