Normalized Database - ONE FOR MANY - Search all merged datasets
I am trying to build a MySQL query that will allow me to pull the title of a song of a track in a database that has both Pop and Electronic genre.
+----------------------------------------------------+
TABLE: SONG
+----------------------------------------------------+
song_id | title |
1 | song name |
+----------------------------------------------------+
TABLE: GENRE
+----------------------------------------------------+
genre_id | name |
1 | Pop |
2 | Electronic |
+----------------------------------------------------+
TABLE: SONG_GENRE
+----------------------------------------------------+
genre_id | song_id |
1 | 1 |
2 | 1 |
This SQL doesn't work as it will obviously never return both genre_id from 1 and 2, but this is where I am stuck.
SELECT DISTINCT song.song_id, song.title
FROM song
LEFT JOIN song_genre ON song_genre.song_id = song.song_id
LEFT JOIN genre ON genre.genre_id = song_genre.genre_id
WHERE genre.genre_id ='1'
AND genre.genre_id='2'
If someone can point me in the right direction, I would be the biggest!
+3
source to share
3 answers
Here's one way to do it:
SELECT DISTINCT song.song_id, song.title
FROM song
INNER JOIN (SELECT songid FROM song_genre WHERE song_genre.genre_id ='1') genre1
ON genre1.song_id = song.song_id
INNER JOIN (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 are no duplicates in song_genre. COUNT (*) = X, where X is equal to the number of genres listed.
SELECT DISTINCT song.song_id, song.title
FROM song
INNER JOIN (SELECT songid, COUNT(*) FROM song_genre
WHERE genre_id IN ('1','2')
GROUP BY songid HAVING COUNT(*) = 2) genre1 ON genre1.song_id = song.song_id
+3
source to share
Assuming the data is normalized and has a character like in your example:
SELECT
song.song_id,
song.title
FROM
song
INNER JOIN song_genre ON song_genre.song_id = song.song_id
INNER JOIN genre ON genre.genre_id= song_genre.genre_id
WHERE
genre.genre_id in ('1', '2')
Modified as per your comment:
SELECT
song.song_id,
song.title
FROM
song
INNER JOIN song_genre ON song_genre.song_id = song.song_id
INNER JOIN genre g1 ON g1.genre_id = song_genre.genre_id
INNER JOIN genre g2 ON g2.genre_id = song_genre.genre_id
WHERE
g1.genre_id = '1' and
g2.genre_id = '2'
+2
source to share