mySQL查询加入与此匹配的关联表这个或这个

Question

I need a little help. Not sure why I can't make sense of this one.

I have a table for songs, genre and an association between the two.

SONGS
song_id
song_name
active (0,1)

GENRE
genre_id
genre_name (rock,jazz,classical,newage,opera)

ASSOC
song_id
genre_id

I can do a simple search like this...

SELECT s.song_name, s.song_id
FROM (songs s)
LEFT JOIN assoc a ON s.song_id = a.song_id
WHERE s.active = 1
AND a.genre_id = 1

And a multi search like this.

SELECT s.song_name, s.song_id
FROM (songs s)
LEFT JOIN assoc a ON s.song_id = a.song_id
WHERE s.active = 1
AND (a.genre_id = 1 
   OR a.genre_id = 2)

BUT, what if I want to get 1 and 2 OR 3? This does not work.

SELECT s.song_name, s.song_id
FROM (songs s)
LEFT JOIN assoc a ON s.song_id = a.song_id
WHERE s.active = 1
AND a.genre_id = 1
AND (a.genre_id = 2 
   OR a.genre_id = 3)

Thanks in advance for help. I have a feeling I am just looking at this the wrong way.

Answer 1

You can perform a self-join:

SELECT s.song_name, s.song_id
FROM   songs AS s
  JOIN assoc AS a1 ON a1.song_id = s.song_id AND a1.genre_id = 1
  JOIN assoc AS a2 ON a2.song_id = s.song_id AND a2.genre_id IN (2,3)
WHERE  s.active = 1