Query Help...Need to find last user

Question

I have a library database where users can borrow media from my inventory list. I am trying to query the database to find out the last user to borrow certain media. Rather than get the last user(s) to borrow the media I get a list of all the users to make a borrow transaction. I believe I am just leaving out something very simple. Please help.

This is my query:
SELECT Users.LastName,Users.UserID,InvTransactions.TransDate FROM InventoryData,InvTransactions,TransToUser,Users WHERE Users.UserID=TransToUser.UserID AND TransToUser.TransactID=InvTransactions.TransactID AND InventoryData.InventoryID=24

Jan Montano · Answer

That shouldn't be the case. I'm assuming there's only one BOOK1. Sam won't be able to loan BOOK1 unless it is returned by john. Although you submitted his second transaction after you loaned sam book1 he must not be included in the result because your condition specifically states BOOK1 with latest transaction date. you gave another book to john but it's not BOOK1. I'm also assuming that with InventoryData.InventoryID=24 it filters records with BOOK1. also do you have some kind of transactiontype with values either 'Borrowed' or 'Returned?' If you have none think of including it in your table.

Maria · Answer

The first example didn't work and the second one came close but it didn't give me the right results. The problem is suppose I have a book called book1 that I loaned to john then john returned book1 and i loaned it to sam. I then gave john another book after i loaned sam book1. when i use this query it shows that john still has book1 because i submitted his second transaction after i loaned sam book1.

Jan Montano · Answer

I'm assuming that InvTransactions.TransDate could be used to get the last borrower of a certain media so we just need to include the transdate in the where clause. SELECT Users.LastNameUsers.UserIDInvTransactions.TransDate FROM InventoryDataInvTransactionsTransToUserUsers WHERE Users.UserID=TransToUser.UserID AND TransToUser.TransactID=InvTransactions.TransactID AND InventoryData.InventoryID=24 AND InvTransactions.TransDate = (SELECT MAX(InvTransactions.TransDate WHERE Users.UserID=TransToUser.UserID AND TransToUser.TransactID=InvTransactions.TransactID AND InventoryData.InventoryID=24) --or you could use the top keyword instead SELECT top 1 Users.LastNameUsers.UserIDInvTransactions.TransDate FROM InventoryDataInvTransactionsTransToUserUsers WHERE Users.UserID=TransToUser.UserID AND TransToUser.TransactID=InvTransactions.TransactID AND InventoryData.InventoryID=24 ORDER BY InvTransactions.TransaDate desc