How to fix SELECT statement to return duplicates

Currently I am trying to return a three table join to find duplicate track titles that are in my a “track” table, and also return the track number | cd title from the other joined tables. My select statement is currently returning all the information from the joined tables but its not showing only the duplicates.

I have also tried using a group by and having clause to try to find a COUNT of comptitle. When I tried that it returned the an empty table.

My Tables:

CREATE TABLE composition (compid, comptitle,...,
    PRIMARY KEY (compid),...);

CREATE TABLE recording (rcdid,..., compid,
    PRIMARY KEY (rcdid, compid),...);

CREATE TABLE cd (cdid, cdtitle,..., 
    PRIMARY KEY(cdid),...);

CREATE TABLE track (cdid, trknum,..., rcdid, compid,
    PRIMARY KEY (cdid, trknum),...);

My statement:

SELECT comptitle, trknum, cdtitle
    FROM track JOIN recording ON track.rcdid = recording.rcdid
    JOIN composition ON recording.compid = composition.compid
    JOIN cd ON cd.cdid = track.cdid;

Output expected | actual:

EXPECTED:
      comptitle      | trknum | cdtitle     
--------------------------------------------
 Cousin Mary         | 2      | Giant Steps 
 Cousin Mary         | 10     | Giant Steps 
 Giant Steps         | 1      | Giant Steps 
 Giant Steps         | 8      | Giant Steps 

ACTUAL:
         comptitle          | trknum |   cdtitle   
----------------------------+--------+-------------
 Giant Steps                | 8      | Giant Steps
 Giant Steps                | 1      | Giant Steps
 Stomp of King Porter       | 1      | Swing
 Sing a Study in Brown      | 2      | Swing
 Cousin Mary                | 14     | Swing
 Cousin Mary                | 10     | Giant Steps

Answer

You can use window functions:

SELECT comptitle, trknum, cdtitle
FROM (SELECT comptitle, trknum, cdtitle,
             COUNT(*) OVER (PARTITION BY comptitle) as cnt
      FROM track t JOIN
           recording r
           ON t.rcdid = r.rcdid JOIN
           composition c
           ON r.compid = c.compid JOIN
           cd 
           ON cd.cdid = t.cdid
     ) t
WHERE cnt >= 2
ORDER BY cnt, comptitle;

Advertisement

Answer