SQL Help

Hello all,

I'm having some difficulty with finding members not in a group. For example, lets say we have an authors table, a books table and a book category table. I want to find all of the authors that have not written a book of a certain category. Since an author can have books in several categories, the typical

SELECT FROM (Books INNER JOIN Authors) INNER JOIN BookCategory WHERE BookCategory <> 'SciFi';

will not work. Is there an easier way to find what I'm looking for without looking through the recordset for members in the BookCategory = 'SciFi' list?

That won't work because an author could have written a SciFi book as well as a Non-Fiction book. If he has, he will still show up in the recordset because of his Non-Fiction book.

I'm looking for the group of authors that have never written a SciFi book (without having to requery the results of my first query looking for authors that have written a 'SciFi' book).

Sorry if I'm not explaining too well, I'm kinda new at this.

Let me clarify a little ...

There is an ASP page with a SQL statement that is querying an Access database. The query that I used to find the authors without SciFi books looks like this:

SELECT FirstName, LastName FROM (Authors as a INNER JOIN Books as b ON a.AuthorID = b.AuthorID) INNER JOIN BookCategory as c ON b.CategoryID = c.CategoryID WHERE c.Category <> 'SciFi'

I had shortened it in my original post for simplicity, but as I stated this will not work if an author has a SciFi book and a Non-Fiction book. The author will show up in the resulting recordset.

I am looking for a way to find all of the authors that have never written a SciFi book without having to look through the results with a second query. If you need any more info, let me know ... I appreciate the assistance.

I'm new at this so it's the language of that additional clause that I'm stumped on. Any suggestions?

Edit: Actually I think I've got it by adding:

AND a.AuthorID NOT IN (SELECT AuthorID FROM Books as bk INNER JOIN BookCategory as ct ON bk.CategoryID = ct.CategoryID WHERE ct.Category = 'SciFi')

It's long, but it works ... although I'm open to suggestions on optimization