help with subquery

[fperez55]

HI- I'm having trouble getting the results I need from an sql query. I believe I can accomplish my task with a subquery however I can't get it to work. I have a table of book titles and a one to many table of books read (that holds many people via ID and the book via ID they read). Now I need to get a list of books left to read by a particular person from my join. So the query starts with a join between the books table and the books read table to get the book titles. I use a left join to get all the book titles even if they haven't been read. However I need it to filter out the books that the particular person has read and leave me with a list of books left to be read by the particular person. Since I'm using a join I need it to ignore the records of the other people so that it will include does books in the results while excluding the books that the particular person has read.


SELECT books.bookID, books.Year, books.bookTitle, bookToread.userID, book_genre_list.Genre AS bookGenreID
FROM (
(
(
(
books
LEFT JOIN bookToread ON bookToread.bookID = books.bookID
)
LEFT JOIN book_genre_list ON books.bookGenreID = book_genre_list.GenreID
)

WHERE (
bookToread.userID IS NULL
OR bookToread.userID NOT
IN (

SELECT bookToread.userID
FROM bookToread
WHERE userID =3
)
)
This is what I've been trying. However it excludes all books read by all users rather that by the user that I need (3).


please advise-frank