Complex Join to Return SuperSet

Question

Evening All,

I have been chipping away at this one for a while and for some reason i just can't seem to get my logic to return the way I expect it to.

I have 3 Data tables as well as 3 business concept linking tables.

• Table1
• Table2
• Table3

Rules:

• Table 1 can be linked to Table 2
• Table 1 can be directly linked to table 3
• Table 1 can be indirectly linked to table 3 via table 2

I have tried a fair few variations however It seems to truncate records.

SELECT
    *
FROM
            Table1          T1
INNER JOIN  Table1_to_Table2_Link   L1  on L1.T1_ID = T1.ID
RIGHT JOIN  TABLE2          T2  ON L1.T2_ID = T2.ID
INNER JOIN  Table2_to_Table3_Link   L2  ON L2.T2_ID = T2.ID
Right JOIN  Table3          T3  ON L2.T3_ID = T3.ID
INNER JOIN  Table1_to_Table3_Link   L3  on T1.ID = L3.T1_ID

Its a bit awkward to explain but in summart I require All the Data from Table 1 And only the Data in Tables 2 and 3 if they are directly/indirectly related to table 1. Tables 2 and 3 don't necessarily have to have a related business concept.

The Return Expected is; Any assistance would be kindly appreciated

Answer 1

You were right. It was not that simple. However I could get desired output by below query

SELECT
T1.*,
T2.*,
T3.*
FROM
        Table1  T1
LEFT JOIN  Table1_to_Table2_Link   L1  on T1.ID = L1.T1_ID
LEFT JOIN  TABLE2          T2  ON T2.ID = L1.T2_ID 
LEFT JOIN (
    SELECT T1_ID AS ID,T3_ID AS table3Id FROM dbo.Table1_to_Table3_Link
    UNION ALL
    SELECT T2_ID AS ID,T3_ID AS table3Id FROM dbo.Table2_to_Table3_Link
    ) S
    ON T1.ID = s.ID 
    OR t2.ID = s.id
LEFT JOIN dbo.Table3 T3 ON S.table3Id = T3.ID

Hope it helps.