title: SQL Server - query with multiple joins dependent of first or second join
body: <p>I have a query with 4 tables. It needs to return the value from the fourth table by joining the first table to either the second or the third table depending on if the join is on the second returned a match. </p>
  
  <p>Here's what I tried after looking this up online:</p>
  
  <pre><code>SELECT t1.field1, t4.field1   
  FROM t1
  LEFT JOIN t2 ON t1.field1 = t2.field1
  LEFT JOIN t3 ON t1.field1 = t3.field1   
  LEFT JOIN t4 ON COALESCE(t2.field1, t3.field1)
  </code></pre>
  
  <p>Right now, it only returns a value from the fourth table when a match is made from the join between t1 and t2.</p>
  
  <p>Is there anyway to achieve this?
  Thanks in advance for your suggestions</p>