NOT EXISTS Simple Query

[hafa]

Server version: 5.1.49

 

raw MySQL statement:

 

SELECT *
FROM tblbook
WHERE NOT
EXISTS 
(SELECT * FROM tblbook LEFT JOIN tblbook_user ON bookID = book_user_bookID 
WHERE book_user_userID =1)

 

No Errors

 

Table Structure:

 

• tbbook: listing of books Primary Key: bookID
  
• tbluser: listing of borrowers: Primary Key: userID
  
• tblbook_user: Join table: listing of books currently in use: Primary Key:  book_userID, Foreign Key: book_user_bookID; Foreign Key: book_user_userID
  

 

Explanation: I'm trying to get a listing of those books not currently in use by a specific user. For this test, I've hard coded the user ID, 1. There are 2 records in tblbook, 1 record in tbluser and 1 record in tblbook_user (one (the only) user has borrowed one of the two books).

 

Current outcome: The SQL statement above returns zero rows, rather than the one row I expect.

 

What's been tried:

 

[*]I tried removing the "NOT"; this returns two rows (both books).

[*]I tried adding "DISTINCT"

[*]I tried using the join statement on both sides of the EXISTS statement - this returned 3 rows, with the borrowed book appearing twice and the available one appearing once.

[*]I tried substituting INNER JOIN as well as a simple = statement

 

Any assistance is greatly appreciated.

 

[gizmola]

The names of your key columns are bizarre.  Why didn't you just use tbbook_bookID as an example, as a foreign key to tbbook?

 

There's numerous ways to do this, but this one is very simple:

 

SELECT * from tblbook WHERE bookID NOT IN (SELECT book_user_bookID FROM tblbook_user WHERE book_user_userID = 1)

[hafa]

SELECT * from tblbook WHERE bookID NOT IN (SELECT book_user_bookID FROM tblbook_user WHERE book_user_userID = 1)

 

Same results; brings up all books (2 records), not just the one book not referred to in the join table.

[hafa]

Figured it out:

 

SELECT * FROM tblbook WHERE bookID NOT IN (SELECT bookID FROM tblbook LEFT JOIN tblbook_user ON bookID = book_user_bookID WHERE book_user_userID = 1)

 

Thanks for pointing me in the right direction