We help IT Professionals succeed at work.

Left join?

steven
steven asked
on
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
Comment
Watch Question

developer
Commented:
I loaded all location then simply updated the locations that matched any container location.  thanks
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:

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

stevendeveloper

Author

Commented:
Thank you Kent. I always appreciate any feedback

Explore More ContentExplore courses, solutions, and other research materials related to this topic.