I have three tables. One is the Item, that contains information like ID and Description.
Another is the "Leave", for when an object is borrowed or goes to maintenace, that contains data like Leave Date, who borrowed it and the id of the item, that is linked to the leave.
The other table is the Return, for when an item is back, it has data like Return Date, who returned it, and the leave id for linking to the leave.
I pass as parameters while generating my report on Java JSF the begin date and the end date for showing the results in that period of time.
I know how to create reports for leaves and reports for returns, but I want to make a combined report that shows both the leaves and the returns, ordered by date, so you can have data shown like this:
Leave Date | Return Date | Quantity Left | Quantity Returned | Object Description
01/01/2018 | ----- | 5 | - | Computer 1 (when leave)
--- | 01/01/2018 | ----- | 5 | Computer 1 (when return)
I tried making a SQL statement combining both data, like this:
SELECT leave.leavedate, leave.whoborrowed, return.returndate, return.whoreturned, item.description, item.id FROM leave INNER JOIN return ON return.leave_id = leave.id INNER JOIN item ON leave.item_id = item.id
But it is just selecting and showing the returns. Can I do what I'm trying to do and if so, what am I missing?