Different results derives from WHERE clause

Screen Link:
https://app.dataquest.io/m/191/guided-project%3A-answering-business-questions-using-sql/6/albums-vs-individual-tracks

My Code:

WITH fti AS
    (
    SELECT il.invoice_id, MIN(il.track_id) first_track_id, t.album_id
    FROM invoice_line il
    JOIN track t ON
        il.track_id = t.track_id
    GROUP BY 1
    )
SELECT fti.*, CASE WHEN
        (
            SELECT il.track_id
            FROM invoice_line il
            WHERE il.invoice_id = (SELECT invoice_id
                                  FROM fti)
        EXCEPT

            SELECT t.track_id
            FROM track t
            WHERE t.album_id = (SELECT album_id
                           FROM fti)
        ) IS NULL
        AND
        (
            SELECT t.track_id
            FROM track t
            WHERE t.album_id = (SELECT album_id
                           FROM fti)
        EXCEPT
            
            SELECT il.track_id
            FROM invoice_line il
            WHERE il.invoice_id = (SELECT invoice_id
                                  FROM fti)
        ) IS NULL
        THEN 'Yes' ELSE 'No' END
        AS album_purchase
FROM fti

What I expected to happen:

What actually happened:

Replace this line with the output/error

If the code is written as follow, it will return what I expect. The only difference between two blocks of codes are at WHERE clauses. In my code, it seems like the comparison only happened with the first result from subqueries and applied for all 614 rows, while the correct code below loop through each result from subqueries. But I don’t understand why, I expected my code should compare with first result from subqueries then second, third, so on… Hope someone can help enlighten me on this! Thank you in advance.

WITH fti AS
    (
    SELECT il.invoice_id, MIN(il.track_id) first_track_id, t.album_id
    FROM invoice_line il
    JOIN track t ON
        il.track_id = t.track_id
    GROUP BY 1
    )
SELECT fti.*, CASE WHEN
        (
            SELECT il.track_id
            FROM invoice_line il
            WHERE il.invoice_id = **fti.invoice_id**
        EXCEPT

            SELECT t.track_id
            FROM track t
            WHERE t.album_id = **fti.album_id**
        ) IS NULL
        AND
        (
            SELECT t.track_id
            FROM track t
            WHERE t.album_id = **fti.album_id**
        EXCEPT
            
            SELECT il.track_id
            FROM invoice_line il
            WHERE il.invoice_id = **fti.invoice_id**
        ) IS NULL
        THEN 'Yes' ELSE 'No' END
        AS album_purchase
FROM fti

@trangnthb10

Hey, where clause doesn’t work that way, if you want to compare it with all values that are retrieved from subqueries, you need to use IN to compare.

for example,
WHERE t.album_id IN (SELECT album_id FROM fti)

Hope it helps!!

1 Like