规范化的数据库-一对多-搜索所有联接的数据集 [英] Normalised database - ONE to MANY - Search through all joined data sets
问题描述
我正在尝试构造一个MySQL查询,该查询将允许我在数据库中同时具有Pop和Electronic类型的曲目中提取歌曲的歌曲标题.
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.
+----------------------------------------------------+
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 |
此SQL无效,因为它显然永远不会返回genre_id 1和2,但这就是我遇到的问题.
This SQL doesn't work, as it is obviously never going to return both a genre_id of 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 somebody could point me in the right direction I would be most greatful!
推荐答案
这里是一种方法:
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
另一种可能更有效的方法.假设song_genre中没有dups. COUNT(*)= X,其中X等于列出的类型.
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.
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
这篇关于规范化的数据库-一对多-搜索所有联接的数据集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!