Need help with sql query to find things tagged with all specified tags

Question

Let's say I have the following tables:

TAGS

id: integer
name: string

POSITIONS

id: integer
body: text

-basic-

id: integer
tag_id: integer
post_id: integer

How can I write a query that selects all messages tagged with ALL of the following tags (attribute attribute of the tag table): "Cheese", "Wine", "Paris", "Frace", "City", "Scenic", "Art"

See also: Need help with sql query to find things with most of the specified tags (note: it seems, but not a duplicate!)

Answer 1

Using IN:

SELECT p.* FROM POSTS p WHERE p.id IN (SELECT tg.post_id FROM TAGGINGS tg JOIN TAGS t ON t.id = tg.tag_id WHERE t.name IN ('Cheese','Wine','Paris','Frace','City','Scenic','Art') GROUP BY tg.post_id HAVING COUNT(DISTINCT t.name) = 7) 

Using JOIN

 SELECT p.* FROM POSTS p JOIN (SELECT tg.post_id FROM TAGGINGS tg JOIN TAGS t ON t.id = tg.tag_id WHERE t.name IN ('Cheese','Wine','Paris','Frace','City','Scenic','Art') GROUP BY tg.post_id HAVING COUNT(DISTINCT t.name) = 7) x ON x.post_id = p.id 

Using EXISTS

 SELECT p.* FROM POSTS p WHERE EXISTS (SELECT NULL FROM TAGGINGS tg JOIN TAGS t ON t.id = tg.tag_id WHERE t.name IN ('Cheese','Wine','Paris','Frace','City','Scenic','Art') AND tg.post_id = p.id GROUP BY tg.post_id HAVING COUNT(DISTINCT t.name) = 7) 

Description

The bottom line is that COUNT(DISTINCT t.name) must match the number of tag names to ensure that all those tags are associated with the message. Without DISTINCT, there is a risk that duplicates of one of the names may return a score of 7 - so you will have a false result.

Performance

Most will tell you that JOIN is optimal, but JOINs also run the risk of duplicating rows in the result set. EXISTS is my next choice - no risk duplication and usually faster execution, but checking the explanation plan will ultimately tell you what is best based on your settings and data.

Answer 2

Try the following:

 Select * From Posts p Where Not Exists (Select * From tags t Where name in ('Cheese', 'Wine', 'Paris', 'Frace', 'City', 'Scenic', 'Art') And Not Exists (Select * From taggings Where tag_id = t.Tag_Id And post_Id = p.Post_Id)) 

The explanation . Please indicate the list of messages that are associated with this set of related tags, equivilent , to request those messages where there is no tag in the same specified set that does not have one . i.e. sql above.