Many to many relationship SQL query

Question

I'm trying to create a query that can fetch results in a many to many relationship database.
So far I got the following:
One table with songs, one table with tags and a 'linking' table as one song can have multiple tags and one tag can belong to multiple songs.

it looks like this:

Songs       Link        Tags
=======     =====       =========
Sid          Sid        Tid
Songname     Tid        Tagname

Now suppose you have 3 songs A B and C and 3 tags: X, Y and Z.
Song A has tag Y, song B has tag Z, and song C has tag X and Z.

I've managed to create a query to get a song by just one tag (e.g. Z gives B and C).
But how do i create a query that searches for songs when mutiple tags are enterred (e.g. typed into a (search) field).

I've searched and a few times the commands INTERSECT and INNER JOIN came up, but i havent been able to succesfully create a query.

Any help is appreciated!

Answer 1

Here's your basic many to many select statement.

select s.*, t.*  
from songs s
join songs_tags st on s.songId = st.songId
join tags t on t.tagId = st.tagId
-- optional where clause
where s.name = 'my song'

When the where clause is tag based, flip it around

select s.*, t.*  
from tags t
join songs_tags st on s.songId = st.songId
join songs s on s.songId = st.songId
-- optional where clause
where t.name = 'whatever'

If you want to return all songs with multiple tags, simply change the where clause:

select s.*
from tags t
join songs_tags st on s.songId = st.songId
join songs s on s.songId = st.songId
where t.name in ('tag1', 'tag2', 'tag3')

Returning a specific song with specific tags, for example, if you wanted to search by name and tag

select s.*, t.*  
from songs s
join songs_tags st on s.songId = st.songId
join tags t on t.tagId = st.tagId
where s.name like '%mysong%' and t.name in ('tag1', 'tag2')

You can also filter the join tables themselves and is sometimes necessary:

select s.*, t.*  
from songs s
join songs_tags st on s.songId = st.songId
join tags t on t.tagId = st.tagId and t.name in ('tag1', 'tag2')
where s.name like '%mysong%'

To find songs without a tag

select s.*
from songs s
left join songs_tags st on s.songId = st.songId
where st.songId is null

To find songs that have all tags:

select s.*
from songs s
join songs_tags st1 on s.songid = st1.songid
join songs_tags st2 on s.songid = st2.songid
join songs_tags st3 on s.songid = st3.songid
where st1.tagid = 1 and st2.tagid = 2 and st3.tagid = 3

Answer 2

You can try this-

select a.Sid, a.SongName, c.Tid, c.TagName  
from Songs a
LEFT OUTER JOIN Links b ON a.Sid = b.Sid 
LEFT OUTER JOIN Tags c ON b.Tid = c.Tid 
WHERE c.TagName IN ('Tag1', 'Tag2', ...... ) // as many tags you want 
group by a.Sid, a.SongName, c.Tid, c.TagName 

Answer 3

This one will get all songs, that match any of the Tags, returning those which match all of them first:

SELECT s.Sid, s.Songname
FROM Songs s
JOIN Link l ON ( l.Sid = s.Sid )
JOIN Tags t ON ( t.Tid = s.Tid )
WHERE t.Tagname IN ( 'X', 'Y' )
GROUP BY s.Sid, s.Songname
ORDER BY COUNT(1) DESC

---

This one will only return those songs, that match all of the Tags:

SELECT s.Sid, s.Songname
FROM Songs s
JOIN Link l ON ( l.Sid = s.Sid )
JOIN Tags t ON ( t.Tid = s.Tid )
WHERE t.Tagname IN ( 'X', 'Y' )
GROUP BY s.Sid, s.Songname
HAVING COUNT(1) = 2 -- 'X' and 'Y'

If you generate this query, you will have to generate the count too (2 in my case).

Answer 4

select distinct songs.* 
from songs 
inner join link on link.sid = songs.sid 
inner join tags on tags.tid = link.tid
where tagname IN ('a','b','c')

something like this?

Answer 5

SELECT DISTINCT s.Sid, s.Songname 
FROM 
    Songs s join Link l on s.Sid=l.Sid 
    join Tags t on t.Tid=l.Tid 
WHERE t.Tagname in ('Z', 'Y');

This will return all songs that have EITHER tag Z or Y