how to write combination of RIGHT OUTER JOIN and LEFT OUTER JOIN

Viewed 11681

Could any one help me to resolve the below query..

Here we have 1. Left outer join 2. Two right outer join

how to combine both right and left O.J and create the same effect as above ,

I need the same query rewritten , this is because I am converting our old ms SQL2009 to ms SQL2012.. and in 2012 *= , =* is not supported.

SELECT X.master_key, X.vendor_code  
FROM 
X, Y, X x2 
WHERE Y.master_key = X.parent_key  
AND Y.master_key *= x2.parent_key 
AND x2.INITIALS =* Y.DEFAULT_INITIALS 
AND x2.VENDOR_CODE =* Y.VENDOR_ABBREV 
AND Y.project_name = 'TEST'  

I have modified above code with my basic knowledge as below and which is not working at all

SELECT X.master_key, X.vendor_code  
FROM 
X, 
Y left outer join X x2 
on Y.master_key = x2.parent_key,

X vnd RIGHT OUTER JOIN Y vnm 
(on vnd.INITIALS = vnm.DEFAULT_INITIALS AND vnd.VENDOR_CODE = vnm.VENDOR_ABBREV )

WHERE Y.master_key = X.parent_key 
AND Y.project_name = 'TEST'  

I am not getting the desired values as original query has to do.

Below is my full original query..

SELECT vnd.master_key, vnd.vendor_code  
FROM 
vnd, vnm, vnd vn2 
WHERE vnm.master_key = vnd.parent_key  
AND vnm.master_key *= vn2.parent_key 
AND vn2.INITIALS =* vnm.DEFAULT_INITIALS 
AND vn2.VENDOR_CODE =* vnm.VENDOR_ABBREV 
AND vnm.inactive = 0 
AND vnd.inactive = 0 
AND vnm.project_name = 'TEST'  
ORDER BY 
lower(vnm.company_name   +  ' '), lower(vnd.vendor_code)  ,lower(vnd.first_name   +  ' '), lower(vnd.last_name   +  ' ')

The solution you gave works but could you do the above one too

SELECT X.master_key, X.vendor_code  
FROM X 
INNER JOIN Y ON X.parent_key = Y.master_key 
LEFT OUTER JOIN X AS x2 ON x2.parent_key = Y.master_key AND x2.INITIALS = Y.DEFAULT_INITIALS AND x2.VENDOR_CODE = Y.VENDOR_ABBREV 
WHERE Y.project_name = 'TEST' 

Regards Janaki

2 Answers
Related