Here's the situation:
Three tables - item( item_id ), staff( staff_id ), item_staff( item_id, staff_id, checked_out_on, returned_on )
All the *_ID columns are numbers and the checked out and returned on are DATE.
These tables are trying to be an item check out system. Basically when someone checks out an item a row that looks like:
Item_ID, Staff_ID, sysdate, NULL
is written to the item_staff table.
When they return it, the returned_on column is updated to be sysdate, which means the item is availilble to be checked out again.
I have a constraint that states that no item can be checked out twice (simply by having a check to see if there is already a NULL value in returned_on for a given item_id), so there are no worries about having two people check out an item at the same time.
My problem is composing a query which will return ALL items and whomever has them checked out (if any).
My original stab at it was:
FROM item i,
WHERE si.item_id (+) = i.item_id
AND si.returned_on IS NULL
AND s.staff_id (+) = si.staff_id
ORDER BY category_name ASC,
That, of course, does not work. As soon as someone returns an item the staff_item row no longer matches 'IS NULL' and the item disappears.
How would I compose a SQL query in Oracle 8 (not 8i) to accomplish this?
In T-SQL I'd stick a correlated subquery in the SELECT i.item_id, ( SELECT ... ) statement but this is not allowed in Oracle.