CODEWITHSUNDEEP
phpmysql
James
James

Reputation: 47

Mysql single query join 3 table and get all the results

Hi i want to list all the song for the album and i want to list all the artist for individual song see below for example.

1. Song Title 1
   - Artist 1, Artist 2, Artist 3    note:(all this individual artist have link to there artist page)
2. Song Title 2
   - Artist 1, Artist 2
3. Song Title 3
   - Artist 1, Artist 2, Artist 3

My tables are song, album, artist, song_artist

song table

+----+-----------+----------+
| id | song_name | album_id |
+----+-----------+----------+
| 1  | Title 1   | 2        |
| 2  | Title 2   | 2        |
| 3  | Title 3   | 2        |
+----+-----------+----------+

album table

+----+------------+
| id | album_name |
+----+------------+
| 1  | Album 1    |
| 2  | Album 2    |
| 3  | Album 3    |
+----+------------+

artist table

+----+-------------+
| id | artist_name |
+----+-------------+
| 1  | Artist 1    |
| 2  | Artist 2    |
| 3  | Artist 3    |
+----+-------------+

song_artist table

+--------+--------------+---------+
| id     | song_id      |artist_id|
+--------+--------------+---------+
| 1      | 1            | 1       |
| 2      | 1            | 5       |
| 3      | 1            | 3       |
| 4      | 2            | 3       |
| 5      | 2            | 1       |
| 6      | 3            | 2       |
| 7      | 3            | 1       |
+--------+--------------+---------+

This is my current code.

$id =$_GET['id']; *// Get album id from url*
$query = "SELECT id, song_name, FROM song WHERE album_id = '".$id."'";
$result = mysql_query($query) or die("h".mysql_error());
while( $song = mysql_fetch_assoc($result)){
echo $song['song_name'];
$result1 = mysql_query("SELECT artist.artist_name as artist_name, artist.id as aid
FROM artist
INNER JOIN song_artist
ON artist.id = song_artist.artist_id
WHERE song_artist.song_id = '".$song['id']."' ");
while($row = mysql_fetch_array($result1)){
echo "<a href='".$row['sid']."'>".$row['artist_name']."</a>, ";
}
}

how do i write mysql single query to get all results in php?

Thank you for your help in advance.

James.

Ok Now i got the result what i want, thank for every one.

$query = "select s.id song_id, s.song_name, group_concat(art.artist_name) artname, a.id
        from song s 
        left outer join album a on a.id = s.album_id
        left outer join song_artist sa on sa.song_id = s.id
        left outer join artist art on art.id = sa.artist_id
        WHERE a.id= '".$id."'
        GROUP BY song_id";

Upvotes: 2

Views: 4580

Answers (3)

Ovais Khatri
Ovais Khatri

Reputation: 3211

Select s.song_name ,art.artist_name,a.album_name from Song
join song_artist sa on sa.Song_id = s.Song_id
join Album a on a.album_id = s.Album_id
join Artist art on art.id = sa.artist_id

Upvotes: 0

Eljakim
Eljakim

Reputation: 6937

This gets you the songs + artists for album 1.

SELECT song_name, group_concat(artist_name) 
FROM song 
LEFT JOIN song_artist ON song.id=song_artist.song_id 
LEFT JOIN artist ON song_artist.artist_id=artist.id 
WHERE song.album_id=1

Note that you did answer this question in another spot as well.

Upvotes: 1

Carlos Torres
Carlos Torres

Reputation: 419

SELECT s.song_name, a.artist_name, al.album_name
FROM artist a
LEFT JOIN song_artist sa ON sa.artist_id = a.id
LEFT JOIN song s ON s.id = sa.song_id
LEFT JOIN album al ON al.id = s.album_id

More info on JOIN

Upvotes: 1

Related Questions