troubleshooting Question

Oracle 10g - LEFT join not returning null rows

Avatar of JDCam
JDCam asked on
Oracle DatabaseSQL
3 Comments1 Solution92 ViewsLast Modified:
I have a simple left join query that is not giving the expected output. I am hoping a second set of eyes can see what I am missing

My main table  should return all rows:
select LOC_CODE, LOC_STAT, from M_LOC where comp_Code = 'A1' and Whse_code = '8' and loc_code like '28-%'

28-11-01	A
28-11-02	A
28-11-03	A
28-11-04	A
28-11-05	A

My second table contains only records matching 2 of the 5 rows
select LOC_CODE, Cust_code from C_LOC where comp_Code = 'A1' and Whse_code = '8' and loc_code like '28-%'

28-11-01	BVFTOR
28-11-03	BVFTOR

My Query with the LEFT Join is behaving like an EQUAL join and only outputting records that exist in the second table
select 
M.LOC_CODE,
C.CUST_CODE
from M_LOC M
LEFT OUTER JOIN C_LOC C on M.COMP_CODE = C.COMP_CODE and M.WHSE_CODE = C.WHSE_CODE and M.LOC_CODE = C.LOC_CODE 
where M.comp_Code = 'A1' and M.Whse_code = '8' and M.loc_STAT = 'A'
and M.loc_code like '28-%'

28-11-01	BVFTOR
28-11-03	BVFTOR

What am I overlooking in this simple query?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros