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?

Edit

WHERE BookCategory <> 'SciFi';

To

WHERE NOT BookCategory = 'SciFi';

I think thats what you mean

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.

fatcity, your SQL Query is not a SQL Query and cannot work like that at all not to talk about showing the correct data.

Anyway, do you Access or SQL Server?
The problem with Access in that case is that Access has problem using OUTER Joins, which would be required. As you want to display Authors that have no Book that is part of a Cartegory that does exist you would go OUTER. So let me know what db u use.

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