Link to home
Create AccountLog in
Avatar of steven
stevenFlag for United States of America

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_num, i.item,it.description, case when c.qty_contained is null then qty_on_hand else c.qty_contained end as qty
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
ASKER CERTIFIED SOLUTION
Avatar of steven
steven
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Kent Olsen

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

Avatar of steven

ASKER

Thank you Kent. I always appreciate any feedback