Many to Many Table SQL Selection Problem

Question

I have two tables that are related, which, for the purpose of this question, will be called posts and tags. posts contains various postings, such as those found on a community forum system. tags contains a unique set of tags, that are added when the server encounters a new tag that is not already in the system. There is only one entry per tag.

posts can have multiple tags, and tags can have more than one post that is a reference to it. To handle these references back and forth, I have created a table to sit in between these two tables, called posttags. posttags contains a reference to the tag id and the post id. This is how the many to many relationship is maintained.

Now, on to the problem. I need to be able to select posts based on a tag. This is a simple join when there is only one tag to search for, but I am at a loss as to how to handle multiple tags. For instance, I need to be able to search the database and get results that have ALL of the tags that are in a list (e.g., "php, mysql, sql"), without using SQL inside of a loop or any other low performance options.

I am not sure how to do this. Can anyone point me in the correct direction?

Thanks!

Answer 1

You can use the following approach, 3 is quantity of requested tags.

SELECT PostId
FROM posttags pt, tags t
WHERE p.Id = pt.PostId
  AND t.Id = pt.TagId
  AND tagname In ('php', 'mysql', 'sql')
group by PostId
having count(*) = 3

or you can sort you requested tags join them in order and use the following:

Select * from Posts p,
(  
 SELECT PostId, GROUP_CONCAT(tagname ORDER BY tag_name DESC SEPARATOR ' ') Tags
 FROM posttags pt, tags t
 WHERE p.Id = pt.PostId
   AND t.Id = pt.TagId
 GROUP BY PostId

) t WHERE t.PostId = p.Id AND t.Tags = 'mysql php sql'

But you need sort your tags

Answer 2

If you want to select the posts which have all tags in a given set, you can use a "counting" strategy:

SELECT
  p.*
FROM tags t
LEFT JOIN posttags pt
  ON pt.tag_id = t.tag_id
LEFT JOIN posts p
  ON p.post_id = pt.post_id
WHERE
  tags.tag IN ('php', 'mysql', 'sql')
GROUP BY p.post_id
HAVING COUNT(tags.tag_id) = 3;

Of course, if you change the set, the number "3" should be changed to the number of elements in the set.