Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606

    Unanswered: Query Problems With Oracle 8

    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:
    Code:
      SELECT i.item_id,
             s.staff_id,
        FROM item          i,
             staff_item    si,
             staff         s
       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,
             item_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.
    Thanks,

    Matt

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    I guess I'll instead make staff_item a history table and then create a new intersection table which will be joined to item to determine who has one checked out.
    Thanks,

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •