chinmaya224
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank You ..
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'