hafa Posted May 11, 2011 Share Posted May 11, 2011 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. Quote Link to comment Share on other sites More sharing options...
gizmola Posted May 12, 2011 Share Posted May 12, 2011 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) Quote Link to comment Share on other sites More sharing options...
hafa Posted May 12, 2011 Author Share Posted May 12, 2011 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. Quote Link to comment Share on other sites More sharing options...
hafa Posted May 12, 2011 Author Share Posted May 12, 2011 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.