user3051442 user3051442 - 7 months ago 11
SQL Question

SQL BillingState != CA

I been at this one for a few hours and am having trouble with the logic.

The question asks:
Find the playlist which have one or more tracks that have never been purchased in California (CA). Print the Id, and the name of such playlists.

The Relationship Sets can be found here:

https://chinookdatabase.codeplex.com/wikipage?title=Chinook_Schema&referringTitle=Documentation

My solutions returns nothing, and whether I am right or wrong I have no idea.
If my solution is incorrect, where is the fallacy in my logic? And if it is wrong, what is the correct way too approach this problem (Solution would be preferable with explanation)

My Solution:

Select DISTINCT P.PlaylistId, P.Name, I.BillingState
From Playlist P, Invoice I, Track T
Where P.PlaylistId IN (
Select PT.PlaylistId
From PlaylistTrack PT
Where PT.TrackId IN(
Select T.TrackId
From Track T
Where T.TrackId IN(
Select IL.InvoiceLineId
From InvoiceLine IL
Where IL.InvoiceId IN(
Select I.InvoiceId
FROM Invoice I
WHERE I.BillingState = 'CA'
Group by I.InvoiceId
Having COUNT(I.InvoiceId) < 1))))
Group By T.TrackId
HAVING COUNT (T.TrackId) > 1

Answer

Let me explain why your code doesn't work. (Darren has a perfectly reasonable answer.)

        Select I.InvoiceId
        FROM Invoice I
        WHERE I.BillingState = 'CA'
        Group by I.InvoiceId
        Having COUNT(I.InvoiceId) < 1

This subquery is logically flawed. It will always return the empty set. Why? You are restricting the invoices to those that have CA. Hence, each invoice will have at least one row before the GROUP BY. Hence, the HAVING clause will filter everything out.

You want conditional aggregation:

        Select I.InvoiceId
        FROM Invoice I
        Group by I.InvoiceId
        Having SUM(CASE WHEN I.BillingState = 'CA' THEN 1 ELSE 0 END) = 0;

But, you probably don't need to aggregate by InvoiceId. It should be unique. So, how about:

        SELECT I.InvoiceId
        FROM Invoice I
        WHERE I.BillingState <> 'CA'
Comments