SQL Server - Conditional Join to whatever table is not null

Question

Badly phrased title my apologies.

I am trying to join a table to one of two other tables

MasterTable SubTable SubTableArchive

---

So, MasterTable contains an ID field. SubTable and SubTableArchive contain a MasterTableId field for the join.

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.

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

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)

So that is working, but its not pretty. Thats a sample, but the real queries are longer and more convoluted.

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.

Answer 1

Another option is to use a UNION and then use INNER JOINs

SELECT M.x, S.x
FROM MAsterTable M INNER JOIN SubTable S ON S.MasterTableId = M.Id 
UNION
SELECT M.x, STA.x
FROM MAsterTable M INNER JOIN SubTableArchive STA ON STA.MasterTableId = M.Id 

From a maintenance point of view, if you make the above union a view, you can then apply where filters and sorts to the view, which should simplify matters.