MySQL query, intersection on many-to-many relationship

Question

Does someone have a good idea/solution how to achieve this?

Situation is: I have the tables 'releases' and 'ntags', related via 'releases_ntags' (containing 'release_id' and 'ntag_id')

And I would like to fetch results for releases via the ntag's 'slug'.

I manage to have this semi-working:

sql

SELECT r.id, r.name
FROM releases r
LEFT JOIN ntags_releases rt ON rt.release_id = r.id
JOIN ntags nt ON nt.id = rt.ntag_id
AND (nt.name = ('TAG_1') OR nt.name = ('TAG_2'))
GROUP BY r.id, r.name

So far so good, but this gives me all releases with "TAG_1" PLUS all releases with "TAG_2" (and off course those with both tags).

But what I need is to only get the intersection of the tags, say:

"releases with 'TAG_1' AND 'TAG_2'"

So i tried with:

...
AND (nt.name = ('TAG_1') AND nt.name = ('TAG_2'))
... 

But this leads in an empty result. Does anyone have an idea how to achieve this? Don't know how to go further on this and would really appreciate some input!

thx

Answer 1

You can demand that two distinct ntags are present in the having clause:

SELECT  r.id, r.name
FROM    releases r
JOIN    ntags_releases rt
ON      rt.release_id = r.id
JOIN    ntags nt
ON      nt.id = rt.ntag_id
WHERE   nt.name in ('TAG_1', 'TAG_2')
GROUP BY
        r.id, r.name
HAVING
        COUNT(distinct nt.name) = 2