Building and Organizing Complex Queries LEFT JOINS

Q&A DQ Courses
, ,
#1

Screen Link:

My Code: 

WITH playlist_info AS
    (
        SELECT
            pt.playlist_id,
            p.name AS playlist_name,
            t.name AS track_name,
            (t.milliseconds / 1000) AS length_seconds
        FROM track AS t
        LEFT JOIN playlist_track AS pt ON t.track_id = pt.track_id
        LEFT JOIN playlist AS p ON pt.playlist_id = p.playlist_id
    )

SELECT
    playlist_id,
    playlist_name,
    COUNT(track_name) AS number_of_tracks,
    SUM(length_seconds) AS length_seconds
    FROM playlist_info
    GROUP BY 1
    ORDER BY 1

What I expected to happen:

Correct Output Table

What actually happened:

Missing rows.

No error, just incorrect data.

Curious why my code does not output the correct data. Other problems don’t seem to have the same issue. Maybe it has to do with using the LEFT JOIN which requires a certain order? The only difference between my answer and the solution is the order of the JOINs. Do I have to switch them around, or is there a different way to fix this by keeping them in the same JOIN order? Any help would be great!

#2

Hello @michaelschneiderny!

This can be a problem in which order your join the columns. You can try this solution using the playlist table instead of the track table:

     FROM playlist p
     LEFT JOIN playlist_track pt ON pt.playlist_id = p.playlist_id
     LEFT JOIN track t ON t.track_id = pt.track_id