MySQL LEFT JOIN多表逻辑问题 [英] MySQL LEFT JOIN multiple tables logic problem

问题描述

我有3个表:singers，songs，albums.它们都与singer_id

I have 3 tables: singers, songs, albums. They are all linked with singer_id

即使没有匹配的singer_id，我也需要全部获取.我设法通过查询获得了所有这些信息:

I need to get all of them even if there is no matching singer_id. I managed to get all of them with this query:

   SELECT singers.singer_name, albums.album_name, songs.song_name 
     FROM singers
LEFT JOIN albums ON singers.singer_id = albums.singer_id 
LEFT JOIN songs ON albums.singer_id = songs.singer_id
    WHERE singer_id = ?

但是问题是我无法正确显示结果.

But the problem is I can;t display the results properly.

假设这位歌手有3张专辑和11首歌曲.这就是我显示它们的方式...

Let's say the singer have 3 albums and 11 songs. This is how i display them...

显示歌手姓名:

echo $results[0]['singer_name']

显示歌曲名称:

foreach($results as $song) {
   // PROBLEM: instead of getting 11 songs i'm getting 33 results so each song show up 3 times.
   echo $song['song_name'] 
}

显示相册:

foreach($results as $album) {
 // PROBLEM: i'm getting 33 albums instead of 3 each album shows up 3 times.
  echo $album['album_name'] 
}

歌曲表引用了专辑表album_id，但是有些歌曲没有专辑，因此我只需要按singer_id

the songs table have a reference to the albums table album_id but there are songs without an album yet so i need to get the results just by the singer_id

我需要得到的是: 11首歌曲. 3张专辑. 1位歌手.

what i need to get is: 11 songs. 3 albums. 1 singer.

提前谢谢.

这个解决方案对我有用. 我想要的是获取指定歌手ID和专辑的所有歌曲 我想要的结果看起来像这样.

this solution worked for me. what i wanted is to get all songs for the specified singer id and albums the results i wanted looks like this.

singer_name : song_name     : album_name
...............................................................
Jay Z       : 99 Problems   : NULL
Jay Z       : Gotta Have It : Watch the Throne

所以我想获得所有歌手的歌，即使它们还没有在专辑中.

so i wanted to get all the singer songs even if they are not in any album yet.

这是我使用的查询.

SELECT singers.singer_name, songs.song_name, albums.album_name
FROM singers
LEFT JOIN songs ON singers.singer_id = songs.singer_id
LEFT JOIN albums ON albums.album_id = songs.album_id
WHERE singers.singer_id = ?

感谢@knittl我得到了我需要的结果.

thanks to @knittl i'm getting the results i need.

但是现在还有另一个问题. 我有3张专辑和11首歌曲

but there is another problem now. i have 3 albums and 11 songs

歌曲正确显示，但是当我在专辑上进行foreach循环时，我又获得了33张专辑....

the songs display correctly but when i make a foreach loop on the albums i get 33 albums again....

如何正确显示相册?我只有3张专辑.

how can i display the albums correctly? i have just 3 albums.

推荐答案

您想要所有歌曲.从歌曲中选择并执行左联接以从其他表中获取潜在数据(按以获得更好的表示形式):

you want all songs. select from songs and perform a left join to get potential data from other tables (order by to get nicer representation):

SELECT a.artist_name, COALESCE(b.album_name, '(no album)'), s.song_name, 
FROM songs s
LEFT JOIN artists a
ON s.singer_id = a.singer_id
LEFT JOIN albums b
ON s.album_id = b.album_id AND s.singer_id = b.singer_id
ORDER BY a.artist_name, b.album_id

这篇关于MySQL LEFT JOIN多表逻辑问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！