Wrong answer with changing order of tables in JOIN


WITH track_info AS
    (SELECT playlist.playlist_id, 
           playlist.name playlist_name,
           (track.milliseconds/1000) length_seconds
    FROM track 
    LEFT JOIN playlist_track ON playlist_track.track_id = track.track_id
    LEFT JOIN playlist ON playlist.playlist_id = playlist_track.playlist_id)
SELECT playlist_id, playlist_name, COUNT(name) number_of_tracks, SUM(length_seconds)length_seconds 
    FROM track_info
    GROUP BY playlist_id,playlist_name
    ORDER BY playlist_id

I was continuously getting wrong answer with my code. I check the answer and the only difference I can locate is the order with which I have written my JOIN statement. I the code above track is joined to playlist_track which is then joined to playlist
How would I figure out which table to place first?

1 Like

Hello @AshmalZahra

When you use FROM track you miss playlist names that are not-musicals. This happens because you did not use FROM playlist as your main table. If you use FROM playlist as your main table, you have all the playlist names even if they are empty playlists with zero tracks. If you use FROM track, you have to join with a key and if this key is missing [an empty playlist], LEFT JOIN creates null values in those cells. This is why you miss them. With FROM playlist as main table, you should get zeroes.

Put the table you want the most important information from as the main table. Personally, I think you should put the table you intend to use its column with GROUP BY as main table.


Like @monorienaghogho suggested, join your other table to the table that contains the most information you need on your final result, in this case [playlist] table.
Then don’t forget to order in ASC

1 Like