Oracle 10g Outer Left Join issue

Posted on 2012-09-20
Last Modified: 2012-09-20

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
Question by:JDCam
    LVL 25

    Accepted Solution

    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
    LVL 25

    Expert Comment

    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)

    Author Closing Comment


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Via a live example, show how to take different types of Oracle backups using RMAN.
    This video shows how to recover a database from a user managed backup

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now