sql join problem

Question

I want to select all posts that contain a specific tag. I'm trying it with this query:

SELECT GROUP_CONCAT(t.tag_name) taglist
FROM posts p
JOIN posts_tags pt ON p.post_id = pt.post_id
JOIN tags t ON t.tag_id = pt.tag_id
WHERE (p.post_private = 0) AND t.tag_name = 'php'
GROUP BY p.post_id

Problem is, the query above, selects all the posts that contain the php tag, but doesn't select any of the other tags a post may contain. Without the AND t.tag_name = 'php' part, it does select every tag a post has, but I want to be able to filter by tag...

Any ideas how to do this? I've tried many things, but can't figure it out...

Sample data without the AND statement:

|| *taglist* ||
|| php,echo  ||
|| c++, cout ||

Sample data with the AND statement:

|| *taglist* ||
|| php       ||

What I want:

|| *taglist* ||
|| php,echo  ||

(the posts that contain the PHP tag only)

Answer 1

I'll try to explain why your first try does not work.

What you really trying to do is to find all posts that one of their tags is 'php'. But their tags are spread in many rows so the t.tag_name = 'php' does not work as it filters out all rows that don't have the 'php' tag.

When you want to check a condition that depends on many rows,

you either create a subquery (to find all post_ids that do have php tag)

  p.post_id IN
  ( SELECT pt2.post_id
      FROM post_tags pt2 
        JOIN tags t2
          ON t2.tag_id = pt2.tag_id
      WHERE t2.tag_name = 'php'
  )

but still join these post_ids with all related tags.

SELECT GROUP_CONCAT(t.tag_name) taglist
  FROM posts p
    JOIN posts_tags pt
      ON p.post_id = pt.post_id
    JOIN tags t
      ON t.tag_id = pt.tag_id
  WHERE (p.post_private = 0) 
    AND p.post_id IN
      ( SELECT pt2.post_id
          FROM post_tags pt2 
            JOIN tags t2
              ON t2.tag_id = pt2.tag_id
          WHERE t2.tag_name = 'php'
      )
  GROUP BY p.post_id

Or you do it a bit more cleverly as Stefanelli showed with 2 more JOINs (that act similarly to a subquery)

Answer 2

SELECT p.post_id, GROUP_CONCAT(t.tag_name) taglist
    FROM posts p
        /* These 2 joins get the list of all tags */
        INNER JOIN posts_tags pt
            ON p.post_id = pt.post_id
        INNER JOIN tags t
            ON pt.tag_id = t.tag_id
        /* These 2 joins guarantee the 'php' tag is included */
        INNER JOIN posts_tags pt2
            ON p.post_id = pt2.post_id
        INNER JOIN tags t2
            ON pt2.tag_id = t2.tag_id
                AND t2.tag_name = 'php'
    WHERE p.post_private = 0
    GROUP BY p.post_id