title: SQL Server - Conditional Join to whatever table is not null
body: <p>Badly phrased title my apologies.</p>
  
  <p>I am trying to join a table to one of two other tables</p>
  
  <p>MasterTable
  SubTable
  SubTableArchive</p>
  
  <hr>
  
  <p>So, MasterTable contains an ID field.
  SubTable and SubTableArchive contain a MasterTableId field for the join.</p>
  
  <p>But, data will only ever exist in one of these SubTables. So I just want to join to whichever table has the data in it.</p>
  
  <p>But the only way I know of doing it is by joining to both and using isnull's on all the fields im selecting, and its quickly becoming complicated to read (and write).
  Especially because some of the fields are already wrapped in ISNULL's</p>
  
  <pre><code>SELECT M.Id, ISNULL(S.Field1, SA.field1), ISNULL(S.field2, SA.Field2),
  SUM(CASE WHEN (ISNULL(S.Finished,SA.Finished)=1 AND ISNULL( ISNULL(S.ItemCode,SA.ItemCode),'')='') THEN 1 WHEN (ISNULL(S.Finished,SA.Finished)=0 AND ISNULL( ISNULL(S.AltItemCode,SA.AltItemCode),'')='') THEN 1 ELSE 0 END) AS SummaryField
  FROM MAsterTable M
  LEFT OUTER JOIN SubTable S ON S.MasterTableId = M.Id
  LEFT OUTER JOIN SubTableArchive SA ON S.MasterTableId = M.Id
  GROUP BY M.Id, ISNULL(S.Field1, SA.field1), ISNULL(S.field2, SA.Field2)
  </code></pre>
  
  <p>So that is working, but its not pretty.
  Thats a sample, but the real queries are longer and more convoluted.</p>
  
  <p>I was hoping SQL might have had some sort of conditional joining functionality built in.
  Something to do what I am trying to do and leave the query itslef a little friendlier.</p>