Combining values from multiple tables using join clause with multiple ON

Question

I want to combine all the related data using LEFT JOIN clause but if one of tables has no matched record from other table it will not show up. Can you check my queries it seems that there is missing or totally messed up. Here's my query.

SELECT*
FROM
MASTER_TBL 
LEFT JOIN
(
SELECT*
FROM
TBLA A
LEFT JOIN
TBLB B
ON
A.ID=B.ID AND A.DESC=B.DESC
LEFT JOIN
TBLC C
ON
B.ID=C.ID AND B.DESC=C.DESC
LEFT JOIN
TBLD D
ON
C.ID=D.ID AND C.DESC=D.DESC
) E
ON 
MASTER_TBL.ID=E.ID

Answer 1

This approach uses UNION ALL to combine the letter named tables (tbla, tblb, tblc, tbld) into a CTE, common table expression. The combined table is then summarized by id, [desc] and crosstabulated (or pivoted) across the login columns. The pivoted result is then LEFT JOIN'ed to the master_tbl. Something like this.

with
tbl_cte(tbl, id, [login], [desc]) as (
    select 'A', * from tbla
    union all
    select 'B', * from tblb
    union all
    select 'C', * from tblc
    union all
    select 'D', * from tblc),
pvt_cte(id, tbla_login, tblb_login, tblc_login, tbld_login, [desc]) as (
    select id, 
           max(case when tbl='A' then [login] else null end) as tbla_login,
           max(case when tbl='B' then [login] else null end) as tblb_login,
           max(case when tbl='C' then [login] else null end) as tblc_login,
           max(case when tbl='D' then [login] else null end) as tbld_login,
           [desc] 
    from tbl_cte
    group by id, [desc])
select mt.id, [name], country, [status], pc.tbla_login, 
       pc.tblb_login, pc.tblc_login, pc.tbld_login, pc.[desc]
from master_tbl mt
     left join pvt_cte pc on mt.id=pc.id;