MySQL query needed.

[Jazztronik]

According to this diagram and its description:

http://i7.photobucket.com/albums/y280/Jazztronik/tables_prod.jpg

I need to retrieve all the products which are unisex. Any help appreciated.

[sridhar_423]

try ...

SELECT * FROM PRODUCTS a WHERE
(SELECT 'SUCCESS' COUNT1 from PRODUCTS_TO_CATEGORIES WHERE PRODUCTS_ID=a.PRODUCTS_FAMILYID group by PRODUCTS_ID HAVING COUNT(PRODUCTS_ID)>1) = 'SUCCESS'

[Jazztronik]

Thank you sridhar! Could you explain me the use of that value or variable 'SUCCESS' COUNT1? I've never seen it before.

[sridhar_423]

COUNT1 is a typo... not required..
coming to "SUCCESS"... it'll be returned as an output of the sub-query only when the count() > 1.. that is there is more than one record in the other table corresponding to each of the rows selected from the main query.

sorry if i hav confused u.. it's slightly difficult to explain.. check if it works for you and let me know..

[Jazztronik]

Yes it works! and don't worry, I understood your explanation. ;)

Thanks again for your help! :)