Query Help...Need to find last user

Maria

17y3k

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jan Montano17y ago
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.
0
Maria17y ago
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.
0
Jan Montano17y ago
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.LastName,Users.UserID,InvTransactions.TransDate FROM InventoryData,InvTransactions,TransToUser,Users 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.LastName,Users.UserID,InvTransactions.TransDate FROM InventoryData,InvTransactions,TransToUser,Users WHERE Users.UserID=TransToUser.UserID AND TransToUser.TransactID=InvTransactions.TransactID AND InventoryData.InventoryID=24
ORDER BY InvTransactions.TransaDate desc
0