MYSQL query in set only

Question

I have a table that associates records with corresponding tags with the following data:

entry_id | tag_id 1 | 1 2 | 1 3 | 1 1 | 2 2 | 2 

I am trying to write a query that returns only tags with tags 1 and 2, in this example, records 1 and 2 will be returned, but 3 will not, because it does not have both tags. The current request that I use works, but I know that it cannot be right:

 SELECT entry_id, GROUP_CONCAT(DISTINCT tag_id ORDER BY tag_id) FROM tags GROUP BY entry_id HAVING GROUP_CONCAT(DISTINCT tag_id ORDER BY tag_id) LIKE "%1,2%"; 

Answer 1

If (entry_id, tag_id) is unique:

 SELECT entry_id FROM yourtable WHERE tag_id IN (1, 2) GROUP BY entry_id HAVING COUNT(*) = 2 

An alternative approach that does not require uniqueness, and can also be faster:

 SELECT T1.entry_id FROM yourtable T1 JOIN yourtable T2 ON T1.entry_id = T2.entry_id AND T1.tag_id = 1 WHERE T2.tag_id = 2 

Answer 2

 SELECT entry_id FROM tags t1 inner join tags t2 on (t1.entry_id = t2.entry_id and t1.tag_id = 1 and t2.tag_id = 2) 

Answer 3

This is a good example for self-join ...

 SELECT tagged1.entry_id FROM tags tagged1 JOIN tags tagged2 ON tagged1.entry_id = tagged2.entry_id AND tagged1.tag_id = 1 AND tagged2.tag_id = 2;