mySQL - Join problem

[murfitUK]

Two tables:1) called "category" with 3 fields: id, kind, description eg0, "Tables", "We have a wide range of tables..."1, "Chairs", "Our large selection of chairs..."etc2) called "details", has numerous fields including one called "type" which has the id from the category table as all products are slotted into a category (so each category could have a large number of products).Trying to do a query to find which categories are not currently used for any products in the details table, but failing miserably. For example, might have in "category":2, "Beds", "All our beds are made from the finest materials..." etcbut if there are no beds in the details table then its an unused category which is what I'm trying to find.I know this should be simple to do but....Please help!

[vijay]

Hi use the following qry:Table: cat - id, cat Table: product - id,cat_id,product_nameSELECT * FROM `cat` WHERE id NOT IN ( SELECT DISTINCT ( cat_id ) FROM product )Regards,Vijay

[murfitUK]

Hi Vijay - thanks for the reply. Sorry, but I still can't get it to work.This works:SELECT DISTINCT(catID) FROM details; - gives value 1,3,4This works:SELECT kind FROM category WHERE id IN (1,3,4); - gives values Chairs, Clocks, LightingAnd this works:SELECT kind FROM category WHERE id NOT IN(1,3,4); - gives value BedsBut I just can't combine the two without getting an error 1064 - you have an error in your syntaxSELECT kind FROM category WHERE id IN (SELECT DISTINCT(catID) FROM details);What am I doing wrong? I've tried the ` round the field names but still an error. I'm using mySQL - is the above only valid for SQL?

[vijay]

hi.. which version of mysql r u using..? subqry support available in >= mysql 4.1 version Regards,Vijay

[murfitUK]

Ah. My local machine is still on 4.0.24, and I don't really want to go about changing things because it was so much trouble getting php, mysql and phpmyadmin all set up originally.The web host company that I use is on 4.1 and I've tried it online - the sub query works just fine.Thanks Vijay, at least I know where the problem lies now.

[aspnetguy]

you could try WAMP or XAMP for local servers.

[nibe49]

Hi murfitUKYou can often do a subquery as a jointrySELECT * FROM category c LEFT JOIN details dON c.id = d.typeWHERE d.type IS NULLregardsnibe