Avatar of JDCam
JDCam

asked on 

Oracle 10g - LEFT join not returning null rows

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

Open in new window


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

Open in new window


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

Open in new window


What am I overlooking in this simple query?
Oracle DatabaseSQL

Avatar of undefined
Last Comment
Alex [***Alex140181***]
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Blurred text
THIS SOLUTION IS 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
Avatar of JDCam
JDCam

ASKER

thank you.... you just gave me a clue and now see the issue.
I am showing you only the top 5 rows of 10,000+
the null records are there, they are just at the bottom.
I added a Order by LOC_CODE and the output is correct.
I have to agree: it seems to be correct... Have you tried using parentheses like this:
select 
M.LOC_CODE,
C.CUST_CODE
from M_LOC M
LEFT OUTER JOIN C_LOC C on (c.COMP_CODE = m.COMP_CODE and c.WHSE_CODE = m.WHSE_CODE and c.LOC_CODE = m.LOC_CODE) 
where M.comp_Code = 'A1' and M.Whse_code = '8' and M.loc_STAT = 'A'
and M.loc_code like '28-%'

Open in new window

Oracle Database
Oracle Database

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo