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:
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)
Select DISTINCT P.PlaylistId, P.Name, I.BillingState
From Playlist P, Invoice I, Track T
Where P.PlaylistId IN (
From PlaylistTrack PT
Where PT.TrackId IN(
From Track T
Where T.TrackId IN(
From InvoiceLine IL
Where IL.InvoiceId IN(
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'