CODEWITHSUNDEEP
sqlsqlite
Jason
Jason

Reputation: 11

Help with SQLITE query

I have three tables Task and TaskTag and Tag as below..

CREATE TABLE Task (task_id text PRIMARY KEY, subject text);
CREATE TABLE TaskTag (task_tag_id text PRIMARY KEY, task_id text, tag_id text);
CREATE TABLE Tag (tag_id text PRIMARY KEY, tag text);

I want to find a record in Task where there are two matching tags: tag1, tag2

How would the SQL look, I have tried but I can't get this to work using AND, but can using OR..

SELECT * 
  FROM Task 
  JOIN TaskTag ON Task.task_id = TaskTag.task_id 
  JOIN Tag ON TaskTag.tag_id = Tag.tag_id 
 WHERE tag = "tag1" 
   AND tag = "tag2"

Upvotes: 1

Views: 45

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332731

Use:

  SELECT * 
    FROM Task 
    JOIN TaskTag ON Task.task_id = TaskTag.task_id 
    JOIN Tag ON TaskTag.tag_id = Tag.tag_id 
   WHERE tag IN ('tag1', 'tag2')
GROUP BY Task.task_id
  HAVING COUNT(DISTINCT tag) = 2

The HAVING COUNT(DISTINCT ...) needs to equal the number of IN parameters, or you risk seeing false positives (IE duplicates of tag1 would count as 2, etc).

Upvotes: 2

Related Questions