DISTINCT help

[patrickin]

I am querying my DB to get some listings.  But because  a listing can fall into up to 5 sub categories, they can be returned up to 5 times by the following query, however I need duplicates removed, via DISTINCT.  However I cannot figure out how to place DISTINCT in this query, so that it actually works, as I am still getting duplicates.  Currently no errors are being thrown out, I'm just getting dupes, and I think my SQL ver and table structure is irrelevant for this question.

 

SELECT DISTINCT listings.*, categories.ID as catID, subcategories.ID as subID, subcategories.categoryID as sccatID FROM listings, categories, subcategories WHERE listings.active = 1 AND subcategories.categoryID = 1 AND categories.ID = 1 AND (listings.subcategory1ID = subcategories.ID OR listings.subcategory2ID = subcategories.ID OR listings.subcategory3ID = subcategories.ID OR listings.subcategory4ID = subcategories.ID OR listings.subcategory5ID = subcategories.ID

 

Thanks for any help!

[dreamwest]

you should still GROUP BY when selecting distinct

 

eg:

 

SELECT DISTINCT * FROM `table` WHERE `title`='funny' GROUP BY `title`

[fenway]

you should still GROUP BY when selecting distinct

 

eg:

 

SELECT DISTINCT * FROM `table` WHERE `title`='funny' GROUP BY `title`

 

No, you shouldn't -- but DISTINCT * is meaningless.

 

In fact, don't ever use DISTINCT -- you'll thank me later.