Search code examples
mysqljoinduplicatesmany-to-many

Select only duplicates from a 3 table join

I have a table like this

comptitle,trknum,cdtitle "A-Tisket, A-Tasket",4,Swing Choo Choo Ch' Boogie,13,Swing Clouds,10,Swing Countdown,11,Giant Steps Countdown,3,Giant Steps Cousin Mary,10,Giant Steps Cousin Mary,14,Swing Cousin Mary,2,Giant Steps Down South Camp Meetin',8,Swing Giant Steps,1,Giant Steps Giant Steps,8,Giant Steps I Know Why,5,Swing It's a Good Enough to Keep,12,Swing Java Jive,7,Swing Mr. P.C.,7,Giant Steps Naima,6,Giant Steps Naima,9,Giant Steps Sing a Study in Brown,2,Swing Sing Moten's Swing,3,Swing Sing You Sinners,6,Swing Skyliner,11,Swing Spiral,4,Giant Steps Stomp of King Porter,1,Swing Syeeda's Song Flute,12,Giant Steps Syeeda's Song Flute,5,Giant Steps Topsy,9,Swing

Which I got from this query

SELECT comptitle, trknum, cdtitle from composition join track on track.compid = composition.compid join cd on cd.cdid = track.cdid group by comptitle, trknum, cdtitle order by comptitle, trknum;

I only want to show the rows where comptitle (the first column) is a duplicate, so the result should be this

comptitle,trknum,cdtitle Countdown,11,Giant Steps Countdown,3,Giant Steps Cousin Mary,10,Giant Steps Cousin Mary,14,Swing Cousin Mary,2,Giant Steps Giant Steps,1,Giant Steps Giant Steps,8,Giant Steps Naima,6,Giant Steps Naima,9,Giant Steps Syeeda's Song Flute,12,Giant Steps Syeeda's Song Flute,5,Giant Steps

I've tried using count, but having count(comptitle) > 1 returns every row instead of just the ones with duplicates.

Solution

  • If not mistaken, table track stores the number of track per composition. In this case, you can create a subquery that gets the compid having multiple tracks,

    SELECT 
    comptitle, trknum, cdtitle 
FROM
    composition
JOIN
    track ON track.compid = composition.compid
JOIN 
    cd ON cd.cdid = track.cdid
INNER JOIN
    (SELECT compid
     FROM track
     GROUP BY compid
     HAVING COUNT(compid) > 1) cc ON composition.compid = cc.compid
GROUP BY
    comptitle, trknum, cdtitle
ORDER BY 
    comptitle, trknum;

    This query can still be simplified, in my opinion, if you will give us the relationship of each table and sample records.