MySQL query, intersection on many-to-many relationship
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
S开发者_如何学编程ELECT 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
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
精彩评论