SQL query help

[rpguk]

I have two linked tables;

simplified db structure said:

categories
catPri | catName

products
proPri | proName | catPri

The two tables are linked by the catPri fields.

There are a few categories which no longer have any products linked to them. I want to export a list of these categories.

Now its easy enough to get a list of categories which products linked to them, but I can't work out how to get a list of categories which don't. I'm sure there is a nice and simple SQL statement that can pull this data out for me, I just seem to have left my brain at home.

Any ideas?

Thanks

Richard.

[Don]

simplified db structure said:

categories
catPri | catName

products
proPri | proName | catPri

select catName
from categories C
where not exists (select * from products P where P.catPri = C.catPri)

or

select catName
from categories C
where (select count(*) from products P where P.catPri = C.catPri) = 0

or (more obscure)

select catName
from categories C
left join products P on P.catPri = C.catPri
where P.proName is null

The last one works as all the fields in the resultset belonging to "products" will be NULL if there are no products records for the category (this is how a left join works). The top query is the neatest I think if your DB supports the syntax.

[rpguk]

Now that was quick! Thanks a lot.