[ACCESS]

[RedBean]

Hi, I have two tables the first one contains the history of book sales and looks something like this:

CUST# BOOK# QTY
12345 A9999 1
12345 B0001 2
98765 A9999 5
99999 C0009 2
...

The second table just contains a subset of books that I am interested in, like:

BOOK#
A9999
G1234
Z1111

What I want to do is to make a query that would return the list of books from the second table that have NOT been purchased by a certain customer. For example if I enter in the customer number 12345, it would return G1234 and Z1111. I can make a query that returns the books on the list that a customer has purchased, but I can't seem to figure out how to return the unpurchased book numbers. I have a feeling it is something really simple, so please don't be too hard on me for not figuring this out. Any help would be appreciated, and thanks in advance.

 

[arb4]

I'm sure there's a more elegant way to address this problem, but if used two queries you could retrieve the info you want. In the first query link the two tables by Book # (simple join) and retrieve the Book# and Cust# and limit to the Cust# you're interested in. Then, in the second query link the "book" table to your first query with a left join (all books, only records from query that match). Retrieve Book# from the book table but this time limit Cust# (from query) to Null values (no need to show/select Cust # since it will just be Null anyway).

Again, this probably isn't the most elegant solution, but the idea is sound even if you were to combine the two queries together (query w/ subquery) in one SQL statement. Perhaps someone better than me at SQL could help with that.

 

[sfvb]

Here is a SQL example that uses the NOT EXISTS clause.

SELECT [BookID]
FROM InterestedBooks
WHERE NOT EXISTS
(SELECT 1
FROM CustomerHistory
WHERE CustomerID = 12345
AND InterestedBooks.BookID = CustomerHistory.BookID);

 

[RedBean]

Thanks for the replies, guys. I am going to use arb4's solution since it runs much faster. I am so glad I stumbled upon this forum, you guys are the greatest.