On Mar 31, 5:59pm, "Tom Cooper"
<tomcoo...@comcast.no.spam.please.net> wrote:
> 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" <d...@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- Hide quoted text -
>
> - Show quoted text -

Worked like a charm, thank you so much!

-Drew