SQL Server - Conditional Join to whatever table is not null

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),'')=''开发者_JS百科) 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.

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.

Use sub-select or better WITH statement (code not tested):

WITH SubTable_WithArchive 
AS (SELECT  Field1, Field2, Finished, ItemCode, AltItemCode, MasterTableID
    FROM    SubTable
    UNION ALL
    SELECT  Field1, Field2, Finished, ItemCode, AltItemCode, MasterTableID
    FROM    SubTableArchive
)
SELECT  M.Id, 
        S.Field1,
        S.Field2,
        SUM(CASE
                WHEN s.Finished = 1 AND ISNULL(s.ItemCode, '') == '' THEN 1
                WHEN s.Finished = 0 AND ISNULL(s.AltItemCode, '') == '' THEN 1
                ELSE 0
            END)
            AS SummaryField

FROM            MasterTable M
LEFT OUTER JOIN SubTable_WithArchive S
            ON  S.MasterTableId = M.Id
GROUP BY        M.Id, S.Field1, s.field2

No, unfortunately, there isn't.

Try this

   SELECT M.Id, S.Field1,S.field2, 
    SUM(CASE WHEN S.Finished=1 AND ISNULL( S.ItemCode,'')='') 
        THEN 1 
        WHEN S.Finished=0 AND ISNULL( S.AltItemCode,'')='') 
        THEN 1 ELSE 0 END) AS SummaryField
    FROM MAsterTable M
    JOIN (
         SELECT id,field1,field2,ItemCode,AltItemCode,finished
         FROM subTable 
         UNION
         SELECT id,field1,field2,ItemCode,AltItemCode,finished
         FROM subTableArchive 
    ) S  ON S.id = M.Id

    GROUP BY M.Id, S.Field1,S.field2

Because a ID value from MasterTable will exist in only one table (SubTable or SubTableArchive) you can use this query:

SELECT MasterTableId Id, Field1, Field2,
SUM(CASE 
WHEN Finished=1 AND ItemCode IS NULL THEN 1 --OR ISNULL(ItemCode,'') = ''
WHEN Finished=0 AND AltItemCode IS NULL THEN 1 --OR ISNULL(AltItemCode,'') = ''
ELSE 0 
END) AS SummaryField
FROM SubTable
GROUP BY 1, 2, 3

UNION ALL

SELECT MasterTableId Id, Field1, Field2,
SUM(CASE 
WHEN Finished=1 AND ItemCode IS NULL THEN 1 --OR ISNULL(ItemCode,'') = ''
WHEN Finished=0 AND AltItemCode IS NULL THEN 1 --OR ISNULL(AltItemCode,'') = ''
ELSE 0 
END) AS SummaryField
FROM SubTableArchive
GROUP BY 1, 2, 3