CODEWITHSUNDEEP
sqlsql-server
ChrisP
ChrisP

Reputation: 10116

How to select records that have multiple values in a related table?

I have the following three tables for tagging content where each content can have one-to-many tags. For example, a content record could have a tag of California and Variable.

Table w/ the content

Content
-ContentID
-ContentName

Table w/ the tags

Tag
-TagID
-TagName

Table that links the content and the tags

ContentTag
-ContentID
-TagID

With the following SELECT statement I want to get records with TagID of both 21 and 54 however no rows are returned.

SELECT * FROM ContentTag
INNER JOIN Content On ContentTag.ContentID=Content.ContentID
INNER JOIN Tag ON ContentTag.TagID=Tag.TagID
Where (Tag.TagID=21 And Tag.TagID=54)

How do I create a SQL SELECT statement to retrieve content that has one-to-many tags?

Upvotes: 2

Views: 2565

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I like to approach this question using aggregation and a having clause:

SELECT c.ContentId, c.ContentName
FROM ContentTag ct INNER JOIN
     Content c
     On ct.ContentID = c.ContentID 
WHERE ct.TagID IN (21, 54)
GROUP BY c.ContentId, c.ContentName
HAVING COUNT(Distinct ct.TagId) = 2;

Some notes:

  • You don't need the join to the tags table. You are using the id and which is in ContentTag.
  • You don't need *. I presume you are looking content that has the two tags.
  • The WHERE clause limits the tags to the two tags in question.
  • The HAVING clause makes sure both are there.

Upvotes: 4

Related Questions