By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
WITH simple_restaurant AS (
SELECT 1 AS id, 'restaurant1' AS restaurant UNION ALL
SELECT 2, 'restaurant2' UNION ALL
SELECT 3, 'restaurant3'
),
simple_category AS (
SELECT 1 AS id, 'ham' AS name UNION ALL
SELECT 2, 'salad'
),
category_restaurant_relation AS (
SELECT 1 AS id, 1 AS category_id, 1 AS restaurant_id UNION ALL
SELECT 3, 1, 2 UNION ALL
SELECT 4, 2, 3 UNION ALL
SELECT 5, 2, 1
)
SELECT sr.id, sr.restaurant
FROM simple_restaurant sr
INNER JOIN category_restaurant_relation csr
ON csr.restaurant_id = sr.id
INNER JOIN simple_category sc
ON sc.id = csr.category_id
GROUP BY
sr.id,
sr.restaurant
HAVING
COUNT(*) FILTER (WHERE sc.name = 'ham') = 0;
| id | restaurant |
|---|---|
| 3 | restaurant3 |