MySQL conditional querying many to many relations (inner joined tables) from an array

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.

items
   id

colors
   id
   name

items_colors
    item_id [foreign key: items(id)]
    color_id [foreign key: colors(id)]

From an array of colors, I would like to get all items that only match one or more provided colors. If an item is also associated with an additional color that is not specified in the array, it should not be retrieved.

SELECT
    `*`
FROM
    `items`
    INNER JOIN `items_colors` ON `items_colors`.`item_id` = `items`.`id`
    INNER JOIN `colors` ON `colors`.`id` = `items_colors`.`color_id`
WHERE
    `colors`.`name` IN('green', 'blue')

In my example above I would like to get all items that match the given array, so all items that have green, or blue, or green and blue color(s). But if an item has the blue and also the red colors (or only red, or no color), it should be excluded from the results.

Currently, I didn’t find a proper way to do it. The query in my example above is retrieving more data than I would expect. Thanks for your help!

Answer

One method uses aggregation:

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 SUM( c.name NOT IN ('green', 'blue') ) = 0;

This doesn’t return the colors, but you could return those with GROUP_CONCAT(c.name).

You could also express the HAVING clause more positively:

HAVING COUNT(*) = SUM( c.name IN ('green', 'blue') )

That said, it is probably more efficient to use NOT EXISTS:

select i.*
from items i
where not exists (select 1
                  from item_colors ic join
                       colors c
                       on ic.color_id = c.id
                  where ic.item_id = i.id and
                        c.name NOT IN ('green', 'blue')
                 );