规范化数据库——从一个到多个——通过所有已连接的数据集进行搜索

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.

我正在构建一个MySQL查询，它将允许我在数据库中提取一个既有流行风格又有电子风格的歌曲标题。

+----------------------------------------------------+
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 is obviously never going to return both a genre_id of 1 and 2 but this is where I am stuck.

这个SQL不起作用，因为它显然永远不会同时返回1和2的genre_id，但这就是我的问题所在。

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!

如果有人能给我指明正确的方向，我将是最伟大的!

3 个解决方案

#1

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 is no dups in song_genre. COUNT(*) = X where X equals the number of genres listed.

另一种可能更有效的方法。这假定在song_genre中没有dup。COUNT(*) = X，其中X等于列出的体裁数量。

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

#2

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

假设数据是标准化的，并且具有与您的样本相同的特征:

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 according to 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'

#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)

如果，那么这意味着在这两种情况下都必须为真，但是每一行只有一个genre_id!也许用“或”或…体裁。genre_id(1、2)

#1