Link to home
Start Free TrialLog in
Avatar of Jeff
JeffFlag for United States of America

asked on

iSeries DB2 SQL - How to select all records for an item where only 1 record meets "Where" criteria?

I need to query my inventory and select all records for all items where 1 of the records for the item has a last received date older than 20160201. My inventory contains over 11,000 items.

For example:

Item 123456 is in inventory on 5 pallets

Pallet 00001 was received 20160105
Pallet 00002 was received 20160605
Pallet 00003 was received 20160605
Pallet 00004 was received 20160705
Pallet 00005 was received 20160705

Currently, my code just grabs Pallet 00001 because the last received rate meets the criteria.

Select tblinv.pallet, tblinv.item, tblitm.description, tblinv.qty, tblinv.aisle, tblinv.slot, tblitm.lastrec
from tblinv
left join tblitm on tblinv.item = tblitm.item
where tblitm.lastrec < '20160201'
order by tblinv.item, tblitm.lastrec

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jeff

ASKER

Worked perfectly! Thank you Kent.