Avoid duplicate rows in SQL query

Question

I use the following SQL query on SQL Server 2008 to select rows from products and categories tables.

SELECT products.idProduct,  sku, description, listPrice, 
   smallImageUrl, isBundleMain, rental, visits 
FROM products, categories_products 
WHERE products.idProduct = categories_products.idProduct 
AND categories_products.idCategory = "& pIdCategory&" 
AND listHidden=0 
AND active=-1 
AND idStore = " &pIdStore& "
ORDER BY description

The problem is that some rows are duplicate. Those duplicates are generally determined by products.idProduct column, so I want to change the query so that the same products.idProduct doesn't appear twice, means for example one of the rows has products.idProduct = 3438 and the other row has same product id as well only one of the products.idProduct gets displayed

Please qualify your select list so that we can determine which field is coming from which table (e.g.: tablename.sku, tablename.description, etc.). Otherwise, it is impossible to tell if a DISTINCT like some have suggested would fix this. Also, please provide sample result set (showing the duplicate records). — Chains
– Chains, Commented Mar 13, 2013 at 19:31

Pieter Geerkens · Accepted Answer · 2013-03-13 18:52:30Z

10

You need to use distinct. Try below

SELECT distinct 
  products.idProduct, sku, description, listPrice, smallImageUrl, 
  isBundleMain, rental, visits 
FROM products, categories_products 
WHERE products.idProduct=categories_products.idProduct 
  AND categories_products.idCategory="& pIdCategory&" 
  AND listHidden=0 AND active=-1 
  AND idStore=" &pIdStore& "  
ORDER BY description

edited Mar 13, 2013 at 18:52

Pieter Geerkens

11.9k2 gold badges35 silver badges54 bronze badges

answered Mar 13, 2013 at 18:47

DevelopmentIsMyPassion

3,5914 gold badges36 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

marc_s Over a year ago

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was discontinued with the ANSI-92 SQL Standard (20 years ago!). Please stop using it

Nick.Mc Over a year ago

Bad habits to kick: using DISTINCT when there is an underlying data quality or modelling issue. I would say this is a worse habit than join syntax.

Praveen Nambiar · Accepted Answer · 2013-03-13 18:49:35Z

3

Use DISTINCT as shown below:

SELECT DISTINCT products.idProduct, 
       sku, description, listPrice, 
       smallImageUrl, isBundleMain, rental, visits 
FROM products, categories_products 
WHERE products.idProduct = categories_products.idProduct 
AND categories_products.idCategory = "& pIdCategory&" 
AND listHidden = 0 AND active = -1 
AND idStore =" &pIdStore& "  
ORDER BY description

answered Mar 13, 2013 at 18:49

Praveen Nambiar

4,9022 gold badges24 silver badges32 bronze badges

1 Comment

marc_s Over a year ago

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was discontinued with the ANSI-92 SQL Standard (20 years ago!). Please stop using it

C0L.PAN1C · Accepted Answer · 2013-03-13 18:51:05Z

3

Use DISTINCT in your select query on your fields.

answered Mar 13, 2013 at 18:51

C0L.PAN1C

12.3k4 gold badges52 silver badges70 bronze badges

Collectives™ on Stack Overflow

Avoid duplicate rows in SQL query

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related