CODEWITHSUNDEEP
sqlpostgresql
CamWhis
CamWhis

Reputation: 17

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

Upvotes: 0

Views: 66

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

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;

Upvotes: 1

Rodrigo Oliveira
Rodrigo Oliveira

Reputation: 21

What you need is a subquery to first find the duplicated track titles in track table, then join this to the other tables. This subquery would look like:

  SELECT rcdid, COUNT(*) AS number
  FROM track
  GROUP BY rcdid
  HAVING COUNT(*) > 1

Now, depending on what database engine you're using, you may be able to use a CTE which would make it more readable. If that is your case, you could try:

WITH CTE_DuplicatedTracks AS (
  SELECT rcdid, COUNT(*) AS number
  FROM track
  GROUP BY rcdid
  HAVING COUNT(*) > 1
)
SELECT
  c.comptitle,
  t.trknum,
  cd.cdtitle
FROM
  CTE_DuplicatedTracks dt
  JOIN track t ON
    dt.rcdid = t.rcdid
  JOIN recording r ON
    t.rcdid = r.rcdid
  JOIN composition c ON
    r.compid = c.compid
  JOIN cd
    ON cd.cdid = t.cdid;

If your engine does not support CTEs:

SELECT
  c.comptitle,
  t.trknum,
  cd.cdtitle
FROM
  (
    SELECT rcdid, COUNT(*) as number
    FROM track
    GROUP BY rcdid
    HAVING COUNT(*) > 1
  ) dt
  JOIN track t ON
    dt.rcdid = t.rcdid
  JOIN recording r ON
    t.rcdid = r.rcdid
  JOIN composition c ON
    r.compid = c.compid
  JOIN cd
    ON cd.cdid = t.cdid;

Upvotes: 2

Related Questions