Select post from two tag (AND)

Question

tag

tag_id      tag          post_id 
--------------------------------
1           category1    1
2           category2    1
3           etc          2
4           category3    1

post

post_id title
-------------
1       title1

How can we get title1 because its in the tag category1 && category2

something like

SELECT TITLE 
  FROM post, tag 
 WHERE tag.post_id = post.post_id 
   AND tags.tag = 'category1','category2'

What about post_id=1 instead of comparing with categories? – Sami Nov 10 '12 at 05:37 — Sami, Nov 10 '12 at 05:37

mcriecken · Accepted Answer · 2012-11-10T05:35:39.133

3

In order to check if a title has BOTH 'category1' and 'category2', you can use the following SQL query:

SELECT title 
FROM post 
JOIN tag ON post.post_id = tag.post_id 
WHERE tag.tag_id IN (SELECT tag_id FROM tag WHERE tag = 'category1')
AND tag.tag = 'category2';

To check for other categories you just repeat the WHERE clause:

SELECT title 
FROM post 
JOIN tag ON post.post_id = tag.post_id 
WHERE tag.tag_id IN (SELECT tag_id FROM tag WHERE tag = 'category1')
AND tag.tag_id IN (SELECT tag_id FROM tag WHERE tag = 'category2')
AND tag.tag = 'category3';

This would return results for titles that have all 3 categories.

edited Nov 10 '12 at 05:35

answered Nov 10 '12 at 05:28

mcriecken

3,217
2
20
23

You are correct. I've modified the query so that it will require both categories. You can accomplish this by using a nested SELECT statement. In this case it's getting all IDs that have 'category1' as a tag, and then it's looking to see which of those also have 'category2' as a tag. – mcriecken Nov 10 '12 at 05:34
how about more then two? how would you do it? – user1780413 Nov 10 '12 at 05:36
Depends on the combo, you can just add additional 'where' statements by connecting them with 'AND' (see above). I'll update the query to show you. – mcriecken Nov 10 '12 at 05:36

score 1 · Answer 2 · edited May 23 '17 at 12:18

You can join both tables like this:

SELECT post.* 
FROM post 
LEFT JOIN tag
ON post.post_id = tag.post_id
WHERE tag.tag IN ('category1','category2')
GROUP BY post.post_id
HAVING COUNT(post.post_id) >1;

See this SQLFiddle

Note: If we don't use HAVING clause, It will also return records where any single value exists

See this SQLFiddle

See the similar requirement with similar table structure.

Select post from two tag (AND)

2 Answers2

See this SQLFiddle

See this SQLFiddle

Linked