title: Conditional Join if Exists
body: <p>I need to join two tables together based on a three-column key stack. The problem is sometimes one of the key columns is translated and mapped differently in another table. I will attempt to example my issue using code:</p>
  
  <pre><code>select t1.TQ
    from table1 t1
    left join table2 t2 on t1.comp_cd = t2.comp_cd and t1.plcy_frm = t2.plcy_frm 
                              and t1.val_cd = t2.val_cd
  </code></pre>
  
  <p>The columns "comp_cd" and "plcy_frm" are fine, however the problem is with val_cd. Sometimes the val_cd in table2 does not map correctly to table1 and must go through a third table, table3. Table3 structure is below:</p>
  
  <pre><code>Val_Cd        Mapped_Val_Cd
  A123         A564
  </code></pre>
  
  <p>So -> I need to join on <code>Mapped_Val_Cd</code> value when it exists in <code>Table3</code>, but join on <code>Val_Cd</code> from <code>Table2</code> when <code>Val_Cd</code> does not exist in <code>Table3</code>. </p>
  
  <p>I hope this makes sense - I have tried Case when exists syntax but cannot get that to work.</p>