CODEWITHSUNDEEP
sqlsql-server-2008t-sql
TSCAmerica.com
TSCAmerica.com

Reputation: 5377

Avoid duplicate rows in SQL query

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

Upvotes: 8

Views: 37088

Answers (3)

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

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

Upvotes: 10

C0L.PAN1C
C0L.PAN1C

Reputation: 12233

Use DISTINCT in your select query on your fields.

Upvotes: 3

Praveen Nambiar
Praveen Nambiar

Reputation: 4892

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

Upvotes: 3

Related Questions