I have the following tables:
posts --------------------- id | name --------------------- 1 | First Post 2 | Another post tags --------------------- id | name --------------------- 1 | Tag1 2 | Tag2 3 | Tag3 posts_tags --------------------- post_id | tag_id --------------------- 1 | 2 1 | 3 2 | 1 3 | 2
I'm trying to write a query that (in human readable terms) would say something like 'Find all of the posts which are tagged with tag1 or tag2'.
So I can put multiple tag IDs in and get a lists of posts that have at least one of those tags back.
I've tried this:
SElECT t1.name
FROM posts AS t1 INNER JOIN posts_tags AS t2
ON t1.id = t2.post_id
WHERE t2.tag_id IN(1,2)
But this returns no results. I'm not really sure where to go from here. Is there any way I can do this in one query - i.e without first fetching the posts, then looping through them to fetch the tags?
Use the IN operator:
SELECT post_id
FROM posts_tags
WHERE tag_id IN (1,2,3)
To avoid getting post_id duplicates use the DISTINCT:
SELECT DISTINCT post_id
...
SELECT posts.name FROM posts LEFT JOIN post_tags ON posts.id = post_tags.post_id WHERE post_tags.tag_id = '1' OR post_tags.tag_id = '2'
SELECT
t1.post_id
FROM
posts_tags as t1
INNER JOIN
posts AS t2
ON
t1.post_id = t2.id
WHERE
t2.tag_id IN(1,2)
By Joining the Posts table you now have access to all of the post data, which I assume is what you are looking for.
you have to join posts_tags table also.
SElECT t1.name FROM posts as t1 INNER JOIN posts_tags as t3 on t3.post_id = t1.id INNER JOIN tags AS t2 ON t2.id = t3.tag_id WHERE t2.id IN(1,2)
SQLFiddle answer here.