title: Convert Legacy SQL Outer JOIN *=, =* to ANSI
body: <p>I need to convert a legacy SQL outer Join to ANSI.</p>
  <p>The reason for that being, we're upgrading from a legacy DB instance (2000/5 ?) to SQL 2016.</p>
  <p><strong>Legacy SQL query</strong> :-</p>
  <p>SELECT</p>
  <blockquote>
  <p><em>--My Data to Select--</em></p>
  </blockquote>
  <pre><code> FROM counterparty_alias ca1,  
    counterparty_alias ca2,  
    counterparty cp,  
    party p  
   WHERE cp.code *= ca1.counterparty_code AND
    ca1.alias = 'Party1' AND  
    cp.code *= ca2.counterparty_code AND  
    ca2.alias = 'Party2' AND
    cp.code *= p.child_code AND  
    cp.category in ('CAT1','CAT2')
  </code></pre>
  <p>Here, Party1 and Party2 Are the party type codes and CAT1 and CAT2 are the category codes. They're just data; I have abstracted it, because the values don't really matter.</p>
  <p>Now, when I try to replace the *= with a LEFT OUTER JOIN, I get a huge mismatch on the Data, both in terms of the number of rows, as well as the Data itself.</p>
  <p>The query I'm using is this  :</p>
  <p>What am I doing wrong ?</p>
  <p>SELECT</p>
  <blockquote>
  <p><em>--My Data to Select--</em></p>
  </blockquote>
  <pre><code> FROM 
    counterparty cp  
    LEFT OUTER JOIN counterparty_alias ca1 ON cp.code = ca1.counterparty_code  
    LEFT OUTER JOIN counterparty_alias ca2 ON cp.code = ca2.counterparty_code      
    LEFT OUTER JOIN party p  ON  cp.code = p.child_code
   WHERE  
    ca1.alias = 'Party1' AND  
    ca2.alias = 'Party2' AND
    cp.category in ('CAT1','CAT2')
  </code></pre>
  <p>Clearly , in all the three legacy joins , the cp (counterparty) table is on the Left hand Side of the *=. So that should translate to a LEFT OUTER JOIN WITH all the three tables. However, my solution doesn't seem to to be working
  How can I fix this ? What am I doing wrong here ?</p>
  <p>Any help would be much appreciated.  Thanks in advance  :)</p>
  <p><strong>EDIT</strong></p>
  <p>I also have another query like this :</p>
  <p>SELECT</p>
  <blockquote>
  <p><em>--My Data to Select--</em></p>
  </blockquote>
  <pre><code>FROM dbo.deal d,  
   dbo.deal_ccy_option dvco,  
   dbo.deal_valuation dv,  
   dbo.strike_modifier sm  
  WHERE d.deal_id = dvco.deal_id  
  AND d.deal_id = dv.deal_id      
  AND dvco.base + dvco.quoted *= sm.ccy_pair  
  AND d.maturity_date *= sm.expiry_date  
  </code></pre>
  <p>In this case, both the dvco and d tables seem to be doing a LEFT OUTER JOIN on the same table sm. How do I proceed about this ?
  Maybe join in on the same table and use an alias sm1 and sm2 ?
  Or should I use sm as the central table and change the join to RIGHT OUTER JOIN on dvco and d tables ?</p>