Re: SELECT DISTINCT with JOIN

From: Craig Hoskin (nospam_at_infobahn.co.nz)
Date: 05/13/04 

Date: Fri, 14 May 2004 09:27:50 +1200

Doooh

Always the way, you post a question and find a possible solution :-)

Would this work?

select a.idProduct, a.description,a.descriptionLong,
a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge
from Products a
where exists (select * from categories c, categories_products b where
a.idProduct = b.idProduct AND c.idcategory=b.idcategory)
AND a.active = -1 AND a.homePage = -1 AND a.prodType = 1
ORDER BY a.idProduct DESC

Cheers

Craig

> Hi everyone
>
> Have a problem I would areally appreciate help with.
> I have 3 tables in a standard format for a Bookshop, eg
>
> Products
> Categories
> Categories_Products
>
> the latter allowing me to have products in multiple categories.
>
> Everthing works well except for one annoying little thing.
>
> When an individual product (which is in more than one topcategory) is
added
> to the Shopping Cart it displays twice, because in my select statement I
> have the Category listed. I realise I could remove the TopCategory from
the
> statement and that makes my DISTINCT work as I wanted, but Id prefer to
have
> the TopCategory as it saves me later having to another SQL query (Im
already
> doing one to allow me not to list category in the Statement .... but If I
> can overcome this one ... then I can remove this as well).
>
> Here is my table structure (the necessary bits)
> products
> idProduct int
> ....
>
> categories
> idcategory int
> idParentCategory int
> topcategory int
> ...
>
> categories_products
> idCatProd int
> idProduct int
> idCategory
>
> When I run a query such as
>
> SELECT DISTINCT a.idProduct, a.description,a.descriptionLong,
> a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge,
> c.topcategory
> FROM products a, categories_products b, categories c
> WHERE active = -1 AND homePage = -1
> AND a.idProduct = b.idProduct
> AND c.idcategory=b.idcategory
> AND prodType = 1 ORDER BY a.idProduct DESC
>
> This will return all products as expected, as well as any products which
are
> in more than one TopCategory.
>
> Any ideas how to overcome this would be greatly appreciated.
>
> Cheers
>
> Craig
>
>

Loading