SELECT DISTINCT with JOIN

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

Date: Fri, 14 May 2004 09:15:37 +1200

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