Avatar of steven
steven
Flag 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
SQL

Avatar of undefined
Last Comment
steven

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
steven

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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

steven

ASKER
Thank you Kent. I always appreciate any feedback
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes