Avoid duplicate rows in SQL query

Question

I use the following SQL query in SQL Server 2008 to select rows from the 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 lines are duplicated. These duplicates are usually defined by the column products.idProduct , so I want to change the request so that the same products.idProduct does not appear twice, for example, means that one of the lines has products.idProduct = 3438 , and the other line has the same product identifier as well only one of products.idProduct displayed

Answer 1

You need to use different ones. 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 

Answer 2

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 

Answer 3

Use DISTINCT in your select query in your fields.