0

This will be a simple one for MySQL specialists.

I have one table with songs (songs):

id, song_name

And another one with tags (tags):

id, tag_name

And i have a crosslink table (tagXsong):

id, song_id, tag_id

I need a single query that returns all songs (from the songs table) which match ALL tags within a list of tags (provided as a joined list of IDs such as (57,58,60,63).

So in other words, i want to retrieve songs that have all of the tags in my list of tag ids.

What a shame asking for this!

dani herrera
  • 48,760
  • 8
  • 117
  • 177
SquareCat
  • 5,699
  • 9
  • 41
  • 75
  • Please note: the list of IDs is provided as a string. I ususally use a query such as SELECT * FROM tagXsong WHERE tag_id IN (57,58,60,63) – SquareCat Jan 31 '12 at 15:43
  • See this question and answer, many different ways to achive what you seek, with benchmarks: [How to filter SQL results in a has-many-through relation](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation) – ypercubeᵀᴹ Jan 31 '12 at 15:58

1 Answers1

1
SELECT s.id,s.song_name FROM song s, tagXsong x 
  WHERE s.id=x.song_id AND x.tag_id IN (57,58,60,63) 
  GROUP BY 1,2 HAVING COUNT(*)=4

This will give you all songs that have the four (57,58,60,63) tag_id's linked to them.

dgw
  • 13,418
  • 11
  • 56
  • 54
  • That's it! Wow, i'd never have arrived there myself :) Thanks! One question though - what's that GROUP business about, and why is the comparison to COUNT(*) made? – SquareCat Jan 31 '12 at 15:56
  • @user1018787 I use the grouping to bundle the songs as they would otherwise show up on a single line each. And having done that I choose only those lines that have a count of 4 (meaning all tags are present). I hope that explains it a bit better. – dgw Jan 31 '12 at 16:00
  • After reading your explanation and trying the query without the grouping i think i get it! Thank You! – SquareCat Jan 31 '12 at 16:04