Mysql Many To Many Relationship Query. How To Get All Tags Of Filtered Posts?

I have gone through tons of questions about this issue here in Stackoverflow but I think this is different. What I'm trying to do is give user ability to filter posts by tags so th

Solution 1:

Well, this is the best I can think of at 4:30 AM:

SELECTdistinct tag_id FROM
    (SELECT pt1.post_id FROM pt1
    INNERJOIN tags t1 ON (pt1.tag_id = t1.id)
    WHERE t1.id IN (1, 2)
    GROUPBY pt1.post_id
    HAVINGCOUNT(DISTINCT t1.id) =2) MatchingPosts
INNERJOIN pt2 ON (MatchingPosts.post_id = pt2.post_id)
WHERE (pt2.tag_id NOTIN (1, 2))

The (1, 2) are the tags you're looking for and the count, of course, will have to match the amount of tags you're using to filter.

Here is an example (Notice I slightly changed the data)

Solution 2:

What do you mean expand to only include tag4, tag5, tag6... why not just change your WHERE t.name in ( ) to reflect those tags...

OR... Do you mean it MUST include tags 1, 2, 3 but also any ONE of ( tag4 or tag5 or tag6 )...

If THAT is the case, I would change the where/having to the following...

WHERE t.name IN ('tag1', 'tag2', 'tag3', 'tag4', 'tag5', 'tag6' )
GROUPBY pt.post_id
HAVINGsum( if( t.name in ('tag1', 'tag2', 'tag3' ), 1, 0 )) =3ANDsum( if( t.name in  ('tag4', 'tag5', 'tag6' ), 1, 0 )) >0