Why did it use a LEFT JOIN instead of INNER JOIN here?

Screen Link:
https://app.dataquest.io/m/190/building-and-organizing-complex-queries/3/the-with-clause

My Code:

WITH playlist_info AS
	(SELECT
     	p.playlist_id,
        p.name playlist_name,
     	t.name track_name,
     	(t.milliseconds / 1000) length_seconds
     FROM playlist p
     LEFT JOIN playlist_track pt ON p.playlist_id = pt.playlist_id
     LEFT JOIN track t ON t.track_id = pt.track_id
     )
     
 SELECT 
 	playlist_id,
    playlist_name,
    COUNT(track_name) number_of_tracks,
   SUM(length_seconds) length_seconds
 FROM playlist_info
 GROUP BY 1,2
 ORDER BY playlist_id ASC;

I initially used INNER JOIN (from the track table) in the code above and was getting a message saying “there are fewer options than there should be”

After getting frustrated with why, I looked at the answer and saw where I made the error - needed to do a join from the ‘playlist’ table and also use a LEFT JOIN.

Just wanted to confirm (for my understanding) if it’s because the task said to create a subquery of ALL the playlists.

Hope that makes sense.

Hi Melaine,

Yes, if we want to see every playlist in the resulting table (exactly as the task says), we have to use a LEFT JOIN instead of INNER.

1 Like

Thank you Elena for your help!

1 Like