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