1

So, I've tried to use the following SQL statements to return results from a relatively small database. I have checked the database for duplicate entries but all entries are different. The code is as follows:

SELECT *
  FROM cds
       JOIN releases
         ON cds.cd_id = releases.cd_id
       JOIN bands
         ON releases.band_id = bands.band_id
 WHERE position = '1' 
       AND
       band_name = 'Prodigy';

and also this one...

SELECT title, position 
    FROM cds INNER JOIN releases ON cds.cd_id = releases.cd_id
    WHERE position = '1' 
    AND band_id = '3'

Any reason as to why I'm getting duplicate results returned?

JRD91
  • 139
  • 1
  • 1
  • 9

1 Answers1

0

Try this:

SELECT DISTINCT
    title, position 
FROM cds INNER JOIN releases ON cds.cd_id = releases.cd_id
WHERE position = '1' 
AND band_id = '3'

And do the same with the query above. Just make sure you only SELECT the columns you actually need.

You can run these queries to see why you get duplicate data

SELECT
    cd_id,
    COUNT(1) AS Amount
FROM [releases]
GROUP BY cd_id
HAVING COUNT(1) > 1
ORDER BY Amount DESC, cd_id ASC;

Everything that comes up in that query, is the reason you have duplicate data.

Trafz
  • 636
  • 3
  • 13
  • Yeah that worked great. Any idea why it was happening though? – JRD91 Apr 05 '14 at 19:37
  • @JRD91 - I updated the answer. If you run the 2nd query, you should find the cd_id's from [releases] which cause the duplicate data. If it doesn't return anything, try running the same thing on [cds] instead. – Trafz Apr 05 '14 at 19:54