Join 3 tables in SQLite database

Question

I have 3 tables in SQLite database:

Songs:

_id | name | length | artist_id (foreign key) | album_id (foreign key) 

Artists:

_id | name 

Albums:

_id | name 

I need a query (use it in an Android app) of a table that consists of the following columns:

_id | name | length | artist_id | artist_name | album_id | album_name 

However, I write the following query statement:

SELECT Songs._id, Songs.name, Songs.length, Songs.artist_id, Artists.name, Songs.album_id, Albums.name FROM Songs, Artists, Albums WHERE Songs.artist_id = Artists._id AND Songs.album_id = Albums._id 

but it gives me an empty table. I tried OR instead of AND and it gives incorrect results (every song duplicates in each album, though the artist is correct). How can I fix my query statement to join the 3 tables in a single table?

Answer 1

Using an explicit JOIN instead of an implicit one, the following should get what you want, although it is curious that your implicit join syntax did not return correct results in the first place. I have used a LEFT JOIN, to account for songs which do not have an associated artist or album, but that may not be necessary for your data set and an INNER JOIN could be used instead.

I have also added column aliases to eliminate ambiguity when fetching rows, since you have similar column names in most of your tables (id, name).

SELECT   Songs._id AS song_id,   Songs.name AS song_name,    Songs.length,    Songs.artist_id AS artist_id,    Artists.name AS artist_name,    Songs.album_id AS album_id,    Albums.name AS album_name FROM   Songs   LEFT JOIN Artists ON Songs.artist_id = Artists._id  LEFT JOIN Albums ON Songs.album_id = Albums._id