I have the following tables:

- Users (userId, names, address)
- MovieInfo (movieId, title, genre etc)
- Transactions (transactId, userId,transactionDate)
- TransactionType(TransactionTypeID, StrKeyDesc eg checkout, return, reserved)
- Transaction_Details (transactDetailsId, transactId, TransactionTypeID, movieId, DueDate, DateReturned)

In my application, when a customer checks out a movie, this information is captured both in the Transactions and Transaction_Details and like wise if he returns a movie, this information is also captured in these tables. Now you will realise that a movie will be captured twice -when checked out and when returned/checked in. My dilema is, I wish to do a query so that i can be able to list the movietitles, user/borrower, dateborrowed, due_date, returnedDate, overdue (based on dateborrowed and dueDate). I want to avoid a scenario where a movie appears twice, under checkout and checkin. All these information should be on one line as in the date, the movie was checked out, dueDate, ReturnDate and if overdue or not.

Thanks.

How many copies of one movie title do you have?
You should include in the MovieInfo table the number of copies for the movie title. Then in the Transaction_Details table, state which copy went to the customer so you can track its return.

I hope this will help.

Jorge