Normalised Database - One To Many - Search Through All Joined Data Sets

I am trying to construct a MySQL query that will allow me to pull the song title of a track in the database that has a genre of both Pop and Electronic. +------------------------

Solution 1:

Here's one way to do it:

SELECTDISTINCT song.song_id, song.title
    FROM song
    INNERJOIN (SELECT songid FROM song_genre WHERE song_genre.genre_id ='1') genre1 
         ON genre1.song_id = song.song_id
    INNERJOIN (SELECT songid FROM song_genre WHERE song_genre.genre_id ='2') genre2 
         ON genre2.song_id = song.song_id

Another way that might be more efficient. This assumes there is no dups in song_genre. COUNT(*) = X where X equals the number of genres listed.

SELECTDISTINCT song.song_id, song.title
FROM song
INNERJOIN (SELECT songid, COUNT(*) FROM song_genre 
WHERE genre_id IN ('1','2') 
GROUPBY songid HAVINGCOUNT(*) =2) genre1 ON genre1.song_id = song.song_id

Solution 2:

Assuming the data is normalized and character as in your sample:

SELECT
   song.song_id, 
   song.title
FROM 
   song
      INNERJOIN song_genre ON song_genre.song_id = song.song_id
      INNERJOIN genre ON genre.genre_id= song_genre.genre_id
WHERE 
   genre.genre_id in ('1', '2')

Modified according to your comment:

SELECT
   song.song_id, 
   song.title
FROM 
   song
      INNERJOIN song_genre ON song_genre.song_id = song.song_id
      INNERJOIN genre g1 ON g1.genre_id = song_genre.genre_id
      INNERJOIN genre g2 ON g2.genre_id = song_genre.genre_id
WHERE 
   g1.genre_id ='1'and
   g2.genre_id ='2'

Solution 3:

WHERE genre.genre_id ='1'AND genre.genre_id='2'

is your query right?

if, then this means it have to be true in both cases but you have per row only one genre_id! perhaps chain this with 'OR' or... genre.genre_id IN (1,2)