user3051442 - 2 years ago 77
SQL Question

# SQL BillingState != CA

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

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
``````

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'
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download