MySQL: many to many relationship with multiple AND condition

Question

I have a many-to-many relationship in my MySQL-database with three tables like this:

TABLE item, TABLE relation (storing only ids of items and tags), TABLE tag

Each item can have multiple tags also tags can be related to multiple items, e.g. the item "shoe" can have the tags "sport" and "leather" while the tag "sport" could be related to items "shoe" and "shirt".

What I need to do now is select all items that have one or many tags combined by an AND-condition, so e.g. I'd like to find all items that have the tag "sport" AND the tag "leather" related.

Is it possible to retrieve the requested data with an AND-condition using just one query or will I need to build subqueries (or something like that)?

The alternative is to get all items with the tags combined with OR like this:

SELECT *
FROM item
LEFT JOIN relation
    ON item.id = item_id
    LEFT JOIN tag
    ON tag.id = tag_id
WHERE (tag = 'sport' OR tag = 'leather')
GROUP BY item.id;

and then filtering out the ones that don't have all necessary tags, but I'd rather have the database do the work instead of fetching unnecessary items and then iterating.

score 3 · Accepted Answer · answered Sep 12 '18 at 07:53

You may slightly alter your current query to get the results you want:

SELECT i.id, i.name    -- OK to select name assuming id is the PK
FROM item i
LEFT JOIN relation r
    ON i.id = r.item_id
LEFT JOIN tag t
    ON t.id = r.tag_id
WHERE t.tag IN ('sport', 'leather')
GROUP BY i.id
HAVING COUNT(DISTINCT t.tag) = 2;

This would return all items which have both sport and leather tags. It works by aggregating by item (as you were already doing), but then asserting in a HAVING clause that there are two distinct matching tags. Since we restricted to only these two tags in the WHERE clause, if the HAVING check passes after aggregation, it implies that the item is a match.

score 2 · Answer 2 · answered Sep 12 '18 at 07:54

You need to use the HAVING command, and yes, complexity will rise to the level of sub queries quickly...

SELECT item.id,
COUNT(*) AS cnt
FROM item
LEFT JOIN relation
    ON item.id = item_id
    LEFT JOIN tag
    ON tag.id = tag_id
WHERE (tag = 'sport' OR tag = 'leather')
GROUP BY item.id
HAVING
 cnt >= 1

;

MySQL: many to many relationship with multiple AND condition

2 Answers2

Linked