Outer Join with Condition

i am joining 2 tables ..... here i am writing the query

SELECT A.*,B.*
FROM MATL_MSTR A,
         MATL_PROD B,
         MATL_DISPATCH C
WHERE A.MATL_ID = B.MATL_ID AND
            A.MATL_ID = C.MATL_ID(+);

Here i want to put one more condition with the outer join table (MATL_DISPATCH) i.e C.matl_sts = '75'.

IF i am directly using this condition then i am not getting all matl_id. Please help me and explain me how to use a condition in outer join table and that should be executed only when the outer join table has the data for that matl_id.
chinmaya224Asked:
Who is Participating?
 
leclaudeConnect With a Mentor Commented:
Put Matl_Dispatch in a subquery.  This should do the trick.

SELECT A.*,B.*
FROM MATL_MSTR A,
         MATL_PROD B,
         (SELECT * FROM MATL_DISPATCH C WHERE C.MATL_STS='75') C
WHERE A.MATL_ID = B.MATL_ID AND
            A.MATL_ID = C.MATL_ID(+)
0
 
Pratima PharandeCommented:
Not get what exactly you want
but as per understaning you want to include the data from table  MATL_DISPATCH C
if it excists with condition C.matl_sts = '75'

then try this

SELECT A.*,B.* , C.*
FROM MATL_MSTR A Inner join
         MATL_PROD B on A.MATL_ID = B.MATL_ID,
         Left join MATL_DISPATCH C On A.MATL_ID = C.MATL_ID and C.matl_sts = '75'
0
 
chinmaya224Author Commented:
Thank You ..
0
All Courses

From novice to tech pro — start learning today.