-
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?
-
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
-
Forum Rules
|
|