Print Page | Close Window

SQL Help

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=7163
Printed Date: 29 March 2026 at 10:25am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com


Topic: SQL Help
Posted By: fatcity101
Subject: SQL Help
Date Posted: 11 November 2003 at 10:03am

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?

 




Replies:
Posted By: Mart
Date Posted: 11 November 2003 at 12:34pm

Edit

WHERE BookCategory <> 'SciFi';

To

WHERE NOT BookCategory = 'SciFi';

I think thats what you mean



Posted By: fatcity101
Date Posted: 11 November 2003 at 3:20pm

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.

 



Posted By: michael
Date Posted: 11 November 2003 at 8:15pm

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.



-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker


Posted By: fatcity101
Date Posted: 12 November 2003 at 7:00am

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.



Posted By: ljamal
Date Posted: 12 November 2003 at 8:34am
you need to add an additional clause to the query which removes the authors that have authored SciFi books and non SciFi. Your current query just removes the books that are non SciFi and doesn't take into account authors that have written multiple books one which at least one is SciFi and at least one non SciFi.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming


Posted By: fatcity101
Date Posted: 12 November 2003 at 9:20am

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




Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net