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?

Solution 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

Solution 2

Try this select, may by the Artists is more important than others, so the Songs come trough Artists and Albums from Songs.

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 
 Artists
 LEFT JOIN Songs ON Songs.artist_id = Artists._id
 LEFT JOIN Albums ON Songs.album_id = Albums._id

Also if there is no entry in Songs belonging to a particular artist or no entry in Albums belonging to a particular song, you will still get the artist entry thanks to the LEFT JOIN. If you would like to return only artists with songs and albums, use JOIN instead.