Oracle 10g Outer Left Join issue

Experts,

I have having issues with what should be a simple outer left join. Please comment what is wrong


Select H.Item_Code, H.Item_Des1, D.item_qty_bkd_qty
from M_item_H H
LEFT OUTER JOIN M_ITEM_D1 D on H.Item_code = D.Item_code
and H.cust_code = D.cust_code
and H.comp_code = D.comp_code
where D.item_qty_Bkd_lev_num = 2
and h.comp_Code = 'W1'
and h.Cust_Code = 'TIMWEB'

Expecting all records from H
and only matching from D, null otherwise

Current output is = and returns only values where
where D.item_qty_Bkd_lev_num = 2
LVL 1
JDCamAsked:
Who is Participating?
 
lwadwellConnect With a Mentor Commented:
That is correct ... the left outer join returns null to the WHERE clause when no row is found.
The WHERE clause then cancels that by doing D.item_qty_Bkd_lev_num = 2

Use the D.item_qty_Bkd_lev_num = 2 in the ON e.g.
LEFT OUTER JOIN M_ITEM_D1 D on H.Item_code = D.Item_code
AND D.item_qty_Bkd_lev_num = 2
and H.cust_code = D.cust_code
and H.comp_code = D.comp_code
0
 
lwadwellCommented:
If D.item_qty_Bkd_lev_num is never null ... you could also do it the WHERE clause
WHERE (D.item_qty_Bkd_lev_num = 2 or D.item_qty_Bkd_lev_num IS NULL)
0
 
JDCamAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.