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

Answer 1

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