I want to get the post_ID and the thumbnail of the post(s) that have certain multiple tags. My tables are as follow:
table: post
-------------------
post_ID | thumbnail
1 | 1.jpg
2 | 2.jpg
3 | 3.jpg
4 | 4.jpg
5 | 5.jpg
table: tags
-----------
tag_ID | tag
1 | red
2 | yellow
3 | orange
4 | blue
5 | pink
table: post_tags
----------------
post_ID | tag_ID
1 | 1
1 | 2
1 | 4
1 | 5
2 | 2
2 | 3
3 | 4
3 | 1
3 | 2
4 | 5
4 | 4
5 | 1
5 | 3
5 | 5
I currently use something like this, but it is not working: I first get all the tags of posts in an array, and then compare them to find if a post has all the 3 tags mentioned.
SELECT post_ID FROM post_tags
WHERE tag_ID IN ($thisTags[0], $thisTags[1], $thisTags[2], $thisTags[3])
Do I need to join or group or something? I am new to SQL and PHP. Thanks a lot.
If you have a UNIQUE constraint on post_tags(post_ID,tag_ID), and you can verify that the elements of thisTags are unique, then you could do something like this:
SELECT post_ID FROM post_tags
WHERE tag_ID IN ($thisTags[0], $thisTags[1], $thisTags[2], $thisTags[3])
GROUP BY post_ID
HAVING COUNT(1) = 4
This approach REQUIRES that the elements in $thisTags are unique. You can easily work around the uniqueness issue in the post_tags table (but the query above requires that to be unique as well.)
There are other SQL statements, other approaches, that can achieve an equivalent result, but the query above is the most concise. (Some of the other approaches are to use EXISTS predicates, or to use JOIN operations on the sets of post_ID returned for each tag.)