Exclusion of rows with many-to-many relationships

Question

I have three tables: posts, tags and posts_has_tags (which facilitate the many-to-many relationship between posts and tags). A post can have any number of tags.

The 'posts' table has the following columns: idposts text

The 'tags' table has these: idtags name

As for the 'posts_has_tags' table: posts_idposts tags_idtags

What I can't do is come up with a query to select all posts except for those which have a tag (or tags) with a certain value in the 'name' column assigned to them. It seems that it should contain a "NOT EXISTS", but I can't really fully wrap my head around it.

Thank you for your help in advance.

EDIT:

Also, is it possible to restrict the result set to certain tags at the same time? For example:

tags for exclusion: a, b tags for inclusion: c

Post with tags 'a', 'f' doesn't make it into result set (because none if it's tags are included). Post with tags 'a', 'b', 'c' doesn't make it into result set either (because it's 'a' and 'b' are excluded tags). Post with tags 'c', 'f' does make it into result set, because 'c' is the included tag.

FINAL EDIT I finally found a solution that seems to be working and reasonably well-performing: http://www.mysqldiary.com/a-many-to-many-relationship-table-solving-the-exclude-relation-problem/

Answer 1

You can use an anti-join.

SELECT p.* 
FROM posts p
LEFT JOIN post_has_tags pt ON (pt.post_id = p.id)
LEFT JOIN tags t ON (t.id = pt.tag_id AND t.name IN ('test','test1','test2'))
WHERE t.id IS NULL
GROUP BY p.id

If you want to force other tags to be included, you do another join.

SELECT p.* 
FROM posts p
LEFT JOIN post_has_tags pt ON (pt.post_id = p.id)
LEFT JOIN tags t ON (t.id = pt.tag_id AND t.name IN ('a','b'))
INNER JOIN tags t2 ON (t2.id <> t.id AND t2.id = pt.tag_id AND t2.name IN ('c')) 
WHERE t.id IS NULL
GROUP BY p.id

This will prioritize exclusion over inclusion.
If you want to prioritize inclusion then replace the inner join with:

INNER JOIN tags t2 ON (t2.id = pt.tag_id AND t2.name IN ('c'))