Query Help

Hi there,
I need some help 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?
-DrewIf 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 help 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|||On Mar 31, 5:59=A0pm, "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_product=s
> and rewrite the query as (note that I also changed that long list of OR's =to
> an IN). =A0Using the list of OR's will work, but the IN is simpler and eas=ier
> to understand (at least to me).
> SELECT products.idProduct,
> =A0products.sku,
> =A0products.description,
> =A0products.price,
> =A0products.highPrice,
> =A0products.listhidden,
> =A0products.listprice,
> =A0products.serviceSpec,
> =A0products.bToBPrice,
> =A0products.smallImageUrl,
> =A0products.noprices,
> =A0products.sDesc,
> =A0products.stock,
> =A0products.noStock,
> =A0products.pcprod_HideBTOPrice,
> =A0products.FormQuantity,
> =A0products.pcProd_BackOrder,
> =A0products.IDBrand,
> =A0Brands.BrandName,
> =A0products.showInHome
> FROM products
> LEFT OUTER JOIN Brands ON products.IDBrand=3DBrands.IDBrand
> WHERE active=3D-1
> =A0 AND configOnly=3D0
> =A0 and removed=3D0
> =A0 AND products.idProduct IN (6936, 6800, 6935, 8531, 8778, 16881, 16057,=
> =A0 =A0 16587, 16880, 18160)
> ORDER BY products.showInHome ASC, products.visits DESC, products.sales DES=C
> Then if you need to add the condition that the idProduct is found in the
> categories_products table with a categories_products.idCategory =3D 160, j=ust
> add the following to the WHERE clause:
> AND EXISTS (SELECT * FROM =A0categories_products
> =A0 =A0 =A0WHERE products.idProduct=3Dcategories_products.idProduct
> =A0 =A0 =A0 =A0 =A0AND categories_products.idCategory =3D 160)
> Tom
> "Drubage" <d...@.tribalectic.com> wrote in message
> news:fa067b56-5b9d-400d-b111-5c10057cb2b6@.i29g2000prf.googlegroups.com...
>
> > Hi there,
> > I need some help 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=3Dcategories_products.idProduct LEFT OUTER JOIN
> > Brands ON products.IDBrand=3DBrands.IDBrand WHERE active=3D-1 AND
> > configOnly=3D0 and removed=3D0 AND (products.idProduct =3D 6936 OR
> > products.idProduct =3D 6800 OR products.idProduct =3D 6935 OR
> > products.idProduct =3D 8531 OR products.idProduct =3D 8778 OR
> > products.idProduct =3D 16881 OR products.idProduct =3D 16057 OR
> > products.idProduct =3D 16587 OR products.idProduct =3D 16880 OR
> > products.idProduct =3D 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 =3D 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?
> > =A0 -Drew- Hide quoted text -
> - Show quoted text -
Worked like a charm, thank you so much!
-Drew