asked on Left join?
we are trying to pull all locations from the PA1 that have containers, so this query seems to work, problem is that there are locations that DON't have a container, so we need to pull those also. Why will the below not work. PLease help we need this asap.
select i.whse,i.loc,ct.container_
from itemloc_mst i
inner join item_mst it on it.item = i.item
left outer join container_item_mst c on c.item = i.item
left outer join container_Mst ct on ct.container_num = c.container_num and ct.whse = i.whse
where i.qty_on_hand > 0
and i.whse ='pa1' and ct.whse = 'pa1'
and i.loc like '20I01%'
and c.item is null
order by i.loc desc
select i.whse,i.loc,ct.container_
from itemloc_mst i
inner join item_mst it on it.item = i.item
left outer join container_item_mst c on c.item = i.item
left outer join container_Mst ct on ct.container_num = c.container_num and ct.whse = i.whse
where i.qty_on_hand > 0
and i.whse ='pa1' and ct.whse = 'pa1'
and i.loc like '20I01%'
and c.item is null
order by i.loc desc
SQL
Last Comment
steven
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you Kent. I always appreciate any feedback
Hi Steven,
An INNER join on container_item_mst and/or container_mst should give you what you want, too. And it may become necessary if your application stores items in a way that the new data will have characteristics to what you just saw.
Good Luck,
Kent