relational division

Question

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

Answer 1

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;