Do I need multiple queries for this or multiple joins?

[kamy99]

Hi:

I'm working on a musician website , who has many albums and songs. Many songs appear in many albums, for example "Greatest Hits"

To manage that I have created 3 tables: albums, songs, songs_albums.

Albums has all the Album info like: title, year released, etc....
Songs has all the songs info like: title, length, lyrics...

songs_albums is where the relationship between albums and songs defined.
It has albumid, songid, displayOrder

I can successfully retrieve and display all the songs for 1 album with a statement like this:

SELECT * FROM `songs_albums` join albums ON (albums.albumid=songs_albums.albumid) 
join songs ON (songs.songid=songs_albums.songid)
WHERE songs_albums.albumid=4

The #4 is dynamically passed.

So now we need a new page, where I have to display each the Album titles, with corresponding songs.
{from this point I'm lost}

If I remove the WHERE clause, I get multiple album titles....

Do I have to run a query within the query? Should I use DISTINCT?

please help.....

[johanafm]

No, the reason is that you put fields from one table next to fields from another table. Btw, do not use * when you don't need all fields, and even when you do I'd recommend writing those fields out since new could be added in the DB later on, and it also makes the code easier to read (won't have to look at the DB to see what's retrieved).

This query

SELECT a.title AS album, s.title AS song
FROM album AS a INNER JOIN album_song AS a_s ON a.id = a_s.album_id
INNER JOIN song AS s ON s.id = a_s.song_id
ORDER BY album

will obviously give a result like

Judgement		Prelude
Judgement		The farthest star
Judgement		Testament
Northern Light		Monochrome
Northern Light		Call the ships to port
Northern Light		Bullet
Northern Light		Invisible & Silent

But you don't have to show everything from every single row. That's up to you. For example

$last_album = '';
while ($row = $stmt->fetch())
{
	# This row does not belong to same album as the last one
	if ($row['album'] != $last_album)
	{
		echo '<h3>'.$row['album'].'</h3>';
	}
	echo $row['song'] . '<br/>';
}