SQL filter with AND rule spanning multiple rows

Question

I have a MySQL database which looks something like:

item table
| id | name |

item_category link table
| item_id | category_id |

category table
| id | name |

If I want to fetch items that are related to one of many categories I can simply do:

SELECT item.*
FROM item
JOIN item_category ON item_category.item_id = item.id
LEFT JOIN category ON category.id = item_category.category_id
WHERE category.name in ("category_one", "category_two")

However, if I want to get items which are related to all of a list of categories then the problem becomes a little more complicated because the rows returned from my query contain a single category each. How can I write a query which contains only the items which are related to all categories?

I tried writing a query with a nested select like this:

SELECT item.*
FROM item
WHERE EXISTS (
  SELECT item.id
  FROM item_category ON item_category.item_id = item.id
  LEFT JOIN category ON category.id = item_category.category_id
  WHERE item_category.id = item.id
  AND category.name = "category_one"  
)
AND EXISTS (
  SELECT item.id
  FROM item_category ON item_category.item_id = item.id
  LEFT JOIN category ON category.id = item_category.category_id
  WHERE item_category.id = item.id
  AND category.name = "category_two"  
)

But this is incredibly unperformant even with indexes on the relevant fields.

Thank you for any input on this issue.

Answer 1

The typical ways to do this are to either (1) join to the "category" cable once for each value that must be matched, or (2) aggregate your first query (grouping on item) and filter where count(distinct category.name) = number of items in your value list.