I've a colors
table and an items
table, with a many to many relation between these 2 tables (via an items_colors
table). An item can have many colors and a color can have many items. And there is no duplicated colors.
items
id
colors
id
name
items_colors
item_id [foreign key: items(id)]
color_id [foreign key: colors(id)]
For exemple I want to get all items that have exactly a blue and a red color (if it have another color or just one of the 2 colors, it must be ignored).
I'm still not able to get it... For exemple the query below is returning too much items (for exemple an item that have another color in addition to blue and red).
SELECT
i.*,
array_agg(c.name)
FROM
items i
JOIN items_colors ic ON ic.item_id = i.id
JOIN colors c ON c.id = ic.color_id
WHERE
c.name IN ('blue', 'red')
GROUP BY
i.id
HAVING count(DISTINCT c.name) = 2
Here is a fiddle to play with my issue: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=9de03c642da5b018d6f20b44bcf91876
You can use group by
and having
:
SELECT i.*
FROM items i JOIN
items_colors ic
ON ic.item_id = i.id JOIN
colors c
ON c.id = ic.color_id
GROUP BY i.id
HAVING COUNT(*) FILTER (WHERE c.name = ANY (ARRAY['blue', 'red'] ) ) = COUNT(*) AND
COUNT(*) = CARDINALITY(ARRAY['blue', 'red']);
This assumes that neither the data nor the array have duplicates. It can be tweaked to handle that, but it seems unlikely for this type of problem.