CODEWITHSUNDEEP
sqldatabasemany-to-manytags
Paul
Paul

Reputation: 33

Artist-tag database: Getting tags

I'm creating a database that's going to contain various artists which can be tagged with several tags. It's a standard many-to-may relation and my database looks like this: 

artist:  
 ID
 name  

tag:
 ID
 name

tagID:
 tagID
 artistID

Say I've got two tagIDs, X and Y. What I want to do is to find all the tags that have an artist in common with tag X and Y. How do I do this?

Ultimately what I want this to turn into is an interface that let's you find an artist that's tagged with an arbitrary set of tags. To make the process simpler I want to filter out combinations of tags that will not give any result.

Upvotes: 1

Views: 216

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

SELECT T.tagID
  FROM tagID T
  WHERE T.artistID IN (SELECT T1.artistID
                          FROM tagID T1,
                               tagID T2,
                          WHERE T1.tagID = X AND
                                T2.artistID = T1.artistID AND
                                T2.tagID = Y)

Share and enjoy.

Upvotes: 1

APC
APC

Reputation: 146209

This solution uses the INTERSECT set operator to identify TagID records for Artists who have both tag X and tag Y. If you work with a flavour of database which doesn't support that set operator you'll have to use a normal join instead.

select distinct tagid
from   tagid 
where tagid not in ('X', 'Y')
where artistid in 
        ( select artistid
          from tagid 
          where tagid = 'X'
        intersect
        select artistid
          from tagid 
          where tagid = 'Y' )
/

Upvotes: 0

Related Questions