Results 1 to 2 of 2

Thread: Query help

  1. #1
    Join Date
    May 2011
    Posts
    1

    Query help

    So I am new to SQL and I have a project that I am trying to accomplish. I am using a read only account on Oracle SQL Developer to access information from another User's tables, here is the table info.

    User: Inventory

    Table: ORDER_LINE_ITEMS
    columns: ORDER_ID,ITEM_ID,QUANTITY,ADJUSTMENT_TYPE,RELEASED _TO,ENTRY_DATE,ENTERED_BY

    Table:ITEMS
    Columns: ID,NAME,DESCRIPTION,IMAGE_URL,QTY,MIN_QTY,CATEGORY ,MAKE,ACTIVE_YN,NOTES


    I need to write a query that will display the name of the items from inventory that have not been checked out of our system in over a year. This is what I am using so far.

    SElECT name
    from inventory.items
    where exists(
    select unique item_id
    FROM inventory.order_line_items
    where entry_date<='06-MAY-2010'
    )
    order by id ASC

    But I am not getting what I need I am getting a list of items that are older but I need to eliminate the ones that have been seen since may,06 2010. any help?

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    What RDBMS are you using?. You need to use LEFT OUTER JOIN to get items which are never checked out, then filter out the rows that were checked out within last 12 months.

    SELECT name
    from items
    left outer join order_line_items
    on items.item_id=order_line_items.item_id
    where items.item_id not in
    (select distinct item_id
    from order_line_items
    where entry_date between '06-MAY-2010' and '05-MAY-2011')

Posting Permissions

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