Hi, I have trouble getting data from 3 mysql tables

Here are the tables

songs
---------
song_id
album_id
artist_id
song_filename

albums
---------
album_id
album_name

artists
---------
artist_id
artist_name

songs.album_name is even 0 when songs does not have an album.

I need to select songs.song_filename, albums.album_name, artists.artist_name from 3 tables.

Here is a query I came up with


SELECT songs.song_filename, artists.artist_name, albums.album_name
FROM songs
LEFT JOIN artists, albums ON
songs.artist_id = artists.artist_id AND songs.album_id = albums.album_id
WHERE artists.artist_id = albums.artist_id


This query will return songs that have albums only.

Any ideas how to include songs without albums also?

Thanks

Try this:

SELECT songs.song_filename, artists.artist_name, albums.album_name
FROM songs
LEFT JOIN artists, albums ON
songs.artist_id = artists.artist_id AND songs.album_id = albums.album_id
WHERE artists.artist_id = albums.artist_id OR songs.album_id = '0'


I'm not exactly sure if that's what you need, but it should push you along the right track,

Nope, code you provided will always return first entry from songs table (songs.song_filename) and albums.album_name will be always null

Can you post a mysql dump of the database tables?

Originally posted by Pheaton
Can you post a mysql dump of the database tables?

I don't want to release the content of the tables, sorry.

I also tried this


SELECT songs.song_filename, artists.artist_name, albums.album_name
FROM songs
LEFT JOIN artists, albums ON
songs.artist_id = artists.artist_id AND
( songs.album_id = albums.album_id OR songs.album_id =0 )
WHERE artists.artist_id = albums.artist_id
ORDER BY artists.artist_name


But no luck :(

Songs always have artists, but not always albums, right?

How 'bout this...

SELECT songs.song_filename, artists.artist_name, albums.album_name
FROM (songs INNER JOIN artists ON songs.artist_id = artists.artist_id) LEFT JOIN albums ON
songs.album_id = albums.album_id
WHERE artists.artist_id = albums.artist_id

-A

Yes, songs not always have albums meaning songs.album_id = 0

Your query returns songs with albums only.

See, if songs.album_id = 0 then WHERE artists.artist_id = albums.artist_id is not true because there is no albums

Then just remove the WHERE statement

So far I have two queries

Returns songs with albums



SELECT songs.song_filename, artists.artist_name, albums.album_name
FROM songs
LEFT JOIN artists, albums ON
songs.artist_id = artists.artist_id AND songs.album_id = albums.album_id
WHERE artists.artist_id = albums.artist_id


Returns songs without albums


SELECT songs.song_id, songs.song_filename, artists.artist_name
FROM songs
LEFT JOIN artists ON songs.artist_id = artists.artist_id
WHERE ( songs.album_id =0 )
ORDER BY artists.artist_name


But how to combine then?

Originally posted by armstrongecom
Then just remove the WHERE statement

Ah, it works :)

Thanks

Yay!! :)