SQL is getting the product from 4 categories even though I am using distinct?? help

Thanks for looking. I am grabbing products from an mdb which are marked as being "hotdeal" but each product is in several categories and it is showing each one 4 times!! Can anyone see where I have cocked up? many Thanks....

pHotDeal = getUserInput(request.querystring("hotDeal"),2)

mySql="SELECT DISTINCT products.idProduct, sku, description, price, visits, listPrice, length, width, height, smallImageUrl, sales, dateAdded, isBundleMain, rental, map, freeShipping, stock.stock, emailText, deliveringtime FROM products, stock, categories_products WHERE listHidden=0 AND active=-1 AND user1 is null AND idStore=" &pIdStore& " AND products.idStock=stock.idStock AND products.idPro开发者_如何转开发duct=categories_products.idProduct" 

' hot deal
if pHotDeal<>"" then
 mySQL=mySQL&" AND hotDeal=-1"
end if

Why is it not "distinct-ing" ?? :)

Thanks anyone

Try with:

Group By products.idProduct

I found the problem. The "sale" button didn't have a value for the querystring "orderby" . It seems that when I add an orderby value it only shows each product once. Sorry! I know I didn't past the orderby option by bit, I didn't think it had anything to do with it! SO the answer is - there was an empty querystring. Thank you all for your help!!

  SELECT DISTINCT products.idProduct,
                  sku,
                  description,
                  price,
                  visits,
                  listPrice,
                  LENGTH,
                  width,
                  height,
                  smallImageUrl,
                  sales,
                  dateAdded,
                  isBundleMain,
                  rental,
                  map,
                  freeShipping,
                  stock.stock,
                  emailText,
                  deliveringtime
    FROM products, stock, categories_products
   WHERE     listHidden = 0
         AND active = -1
         AND user1 IS NULL
         AND idStore = " &pIdStore& "
         AND products.idStock = stock.idStock
         AND products.idProduct = categories_products.idProduct
         AND categories_product.idCategory = ??
GROUP BY products.idProduct

You need a category id to get 1 row.