NOT IN subquery problem

[PLiNk]

I am trying to join to find publishers that have no registered books. I am moving books from one publisher to another, and need to delete the former publisher.
I want to delete all publisher that have no books attached.
So I select all publisherkeys and subtract the publisherkeys (distinct) I find in the book table.

SELECT distinct Publisher.PublisherKEY
FROM Publisher WHERE publisherKEY NOT IN(select distinct book.publisherKEY FROM book)

This does not work. It does not help to remove the parenthesis around the second query, acccess prompts me for this. Every tutorial I have seen on the internett does not work. I get a Syntax error in the from clause beginning at

Publisher WHERE publisherKEY NOT IN(select distinct book.publisherKEY FROM book)

Can anyone help? Litterally tearing my hair out. Seriously. I know it can be done, I have done it before. Now I am going for a cigarette. Crikey!

Regards JT

 

[PHV]

You may try either this:
SELECT PublisherKEY
FROM Publisher
WHERE NOT publisherKEY IN (select distinct publisherKEY FROM book);
Or this:
SELECT Publisher.PublisherKEY
FROM Publisher LEFT JOIN book ON Publisher.PublisherKEY = book.publisherKEY
WHERE book.publisherKEY Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886