Remaining Records Query

[Scorez2000]

I've been battling with access for the past hour trying to work this one out.

The system has a list of users, and each user has been assigned a list of books each. Each user had to input a comment about each book.

A user may have many books, but each book will only be read by one person. The comments will be tied to the book and each book can only have one comment against it.

Here are my tables.

User: UserID, Username, password
Book: BookID, USerID, Title
Comments: CommentID, BookID, Comment

I have loads of data in the system at the minute, but not all users have filled in all their comments. However, we would like to reward those who have filled in ALL comments against all the books the books assigned to them.

It it possible to write a query to pull out all users that have commented against every book assigned to them?

Thanks in advance.

 

[HarleyQuinn]

I would say to do it like this:
SELECT all of the userid's where there is NOT a comment in a subquery. Then wrap a query around the subquery returning the userid's, title and comment for the userid's that DON't appear in the first query.

If you need any help on doing this I'm sure we could provide you a code sample if required.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[jimirvine]

Hi.
Something like:

SELECT UserName
FROM User As U LEFT OUTER JOIN
(SELECT UserID
FROM Book AS B LEFT OUTER JOIN Comments AS C
ON B.BookID = C.BookID
WHERE C.Comments IS NULL) AS Inner
ON U.UserID = Inner.UserID

This is freehand so it might be a bit off, if it is, post back and I'll build a dummy db and get the SQL right.
HTH
Jim

 

[HarleyQuinn]

Jim's shown pretty much exactly what I was driving at...

Harleyquinn

---------------------------------
For tsunami relief donations

 

[PHV]

List of users who have filled in ALL comments against all the books the books assigned to them:
SELECT * FROM User U
WHERE Not Exists (SELECT * FROM Book B LEFT JOIN Comments C ON B.BookID=C.BookID
WHERE B.USerID=U.UserID AND C.BookID Is Null)

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

 

[jimirvine]

Woops, sorry, make that

SELECT UserName
FROM User As U INNER JOIN
(SELECT UserID
FROM Book AS B LEFT OUTER JOIN Comments AS C
ON B.BookID = C.BookID
WHERE C.Comments IS NULL) AS Inner
ON U.UserID = Inner.UserID