RedHood148 RedHood148 - 6 months ago 18
MySQL Question

DIVISION in SQL query

I'm supposed to write a query for this statement:


List the names of customers, and album titles, for cases where the customer has bought the entire album (i.e. all tracks in the album)


I know that I should use division.

Here is my answer but I get some weird syntax errors that I can't resolve.

SELECT
R1.FirstName
,R1.LastName
,R1.Title
FROM (Customer C, Invoice I, InvoiceLine IL, Track T, Album Al) AS R1
WHERE
C.CustomerId=I.CustomerId
AND I.InvoiceId=IL.InvoiceId
AND T.TrackId=IL.TrackId
AND Al.AlbumId=T.AlbumId
AND NOT EXISTS (
SELECT
R2.Title
FROM (Album Al, Track T) AS R2
WHERE
T.AlbumId=Al.AlbumId
AND R2.Title NOT IN (
SELECT R3.Title
FROM (Album Al, Track T) AS R3
WHERE
COUNT(R1.TrackId)=COUNT(R3.TrackId)
)
);


ERROR:
misuse of aggregate function COUNT()


You can find the schema for the database here

Thank you.

Answer

You cannot alias a table list such as (Album Al, Track T) which is an out-dated syntax for (Album Al CROSS JOIN Track T). You can either alias a table, e.g. Album Al or a subquery, e.g. (SELECT * FROM Album CROSS JOIN Track) AS R2.

So first of all you should get your joins straight. I don't assume that you are being taught those old comma-separated joins, but got them from some old book or Website? Use proper explicit joins instead.

Then you cannot use WHERE COUNT(R1.TrackId) = COUNT(R3.TrackId). COUNT is an aggregate function and aggregation is done after WHERE.

As to the query: It's a good idea to compare track counts. So let's do that step by step.

Query to get the track count per album:

select albumid, count(*)
from track
group by albumid;

Query to get the track count per customer and album:

select i.customerid, t.albumid, count(distinct t.trackid)
from track t
join invoiceline il on il.trackid = t.trackid
join invoice i on i.invoiceid = il.invoiceid
group by i.customerid, t.albumid;

Complete query:

select c.firstname, c.lastname, a.title
from
(
  select i.customerid, t.albumid, count(distinct t.trackid) as cnt
  from track t
  join invoiceline il on il.trackid = t.trackid
  join invoice i on i.invoiceid = il.invoiceid
  group by i.customerid, t.albumid
) bought
join
(
  select albumid, count(*) as cnt
  from track
  group by albumid
) complete on complete.albumid = bought.albumid and complete.cnt = bought.cnt
join customer c on c.customerid = bought.customerid
join album a on a.albumid = bought.albumid;