title: how to write combination of RIGHT OUTER JOIN and LEFT OUTER JOIN
body: <p>Could any one help me to resolve the below query.. </p>
  
  <p>Here we have 
  1. Left outer join
  2. Two right outer join</p>
  
  <p>how to combine both right and left O.J and create the same effect as above , </p>
  
  <p>I need the same query rewritten , this is because I am converting our old ms SQL2009 to ms SQL2012.. and in 2012 <code>*=</code> , <code>=*</code> is not supported.</p>
  
  <pre><code>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'  
  </code></pre>
  
  <p>I have modified above code with my basic knowledge as below and which is not working at all</p>
  
  <pre><code>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'  
  </code></pre>
  
  <p>I am not getting the desired values as original query has to do. </p>
  
  <p>Below is my full original query..</p>
  
  <pre><code>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   +  ' ')
  </code></pre>
  
  <p>The solution you gave works but could you do the above one too</p>
  
  <pre><code>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' 
  </code></pre>
  
  <p>Regards
  Janaki </p>