If I understand what you want, you could just add a DISTINCT to the select.
However, the way I would do it is to remove the join on categories_products
and rewrite the query as (note that I also changed that long list of OR's to
an IN). Using the list of OR's will work, but the IN is simpler and easier
to understand (at least to me).

SELECT products.idProduct,
products.sku,
products.description,
products.price,
products.highPrice,
products.listhidden,
products.listprice,
products.serviceSpec,
products.bToBPrice,
products.smallImageUrl,
products.noprices,
products.sDesc,
products.stock,
products.noStock,
products.pcprod_HideBTOPrice,
products.FormQuantity,
products.pcProd_BackOrder,
products.IDBrand,
Brands.BrandName,
products.showInHome
FROM products
LEFT OUTER JOIN Brands ON products.IDBrand=Brands.IDBrand
WHERE active=-1
AND configOnly=0
and removed=0
AND products.idProduct IN (6936, 6800, 6935, 8531, 8778, 16881, 16057,
16587, 16880, 18160)
ORDER BY products.showInHome ASC, products.visits DESC, products.sales DESC

Then if you need to add the condition that the idProduct is found in the
categories_products table with a categories_products.idCategory = 160, just
add the following to the WHERE clause:

AND EXISTS (SELECT * FROM categories_products
WHERE products.idProduct=categories_products.idProduct
AND categories_products.idCategory = 160)

Tom

"Drubage" <drew@tribalectic.com> wrote in message
news:fa067b56-5b9d-400d-b111-5c10057cb2b6@i29g2000prf.googlegroups.com...
> Hi there,
>
> I need some with a query that is giving duplicate results. The
> query looks like this:
>
> SELECT products.idProduct, products.sku, products.description,
> products.price, products.highPrice, products.listhidden,
> products.listprice, products.serviceSpec, products.bToBPrice,
> products.smallImageUrl, products.noprices, products.sDesc,
> products.stock, products.noStock, products.pcprod_HideBTOPrice,
> products.FormQuantity, products.pcProd_BackOrder, products.IDBrand,
> Brands.BrandName, products.showInHome FROM products JOIN
> categories_products ON
> products.idProduct=categories_products.idProduct LEFT OUTER JOIN
> Brands ON products.IDBrand=Brands.IDBrand WHERE active=-1 AND
> configOnly=0 and removed=0 AND (products.idProduct = 6936 OR
> products.idProduct = 6800 OR products.idProduct = 6935 OR
> products.idProduct = 8531 OR products.idProduct = 8778 OR
> products.idProduct = 16881 OR products.idProduct = 16057 OR
> products.idProduct = 16587 OR products.idProduct = 16880 OR
> products.idProduct = 18160) ORDER BY products.showInHome ASC,
> products.visits DESC, products.sales DESC
>
> The problem is that a product can appear in more than 1 category so
> the same idProduct may appear more than once in the
> categories_products table. I need to use that join in the statement,
> however, as sometimes I add this to the query to further filter
> products:
>
> "AND categories_products.idCategory = 160"
>
> I've tried to do a GROUP BY but with no success, what I really want to
> do is make this query only pull distinct idProduct but obviously I
> cannot just add "SELECT DISTINCT" when there is more than 1 field in
> the query. Any ideas?
>
> -Drew