CODEWITHSUNDEEP
sqlsql-serverleft-joinfull-outer-join
Thirdy Leon
Thirdy Leon

Reputation: 59

Combining values from multiple tables using join clause with multiple ON

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

enter image description here

Upvotes: 1

Views: 1138

Answers (2)

SteveC
SteveC

Reputation: 6015

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;

Upvotes: 0

GMB
GMB

Reputation: 222502

The problem is that you are cascading the conditions across joins. For example, here are the join conditions for table d:

C.ID = D.ID AND C.DESC = D.DESC

For this to match, you need to have a matching row in C already.

As your query stands, it looks like you can use the id from the master table to search all the following tables. As for the desc columns, it looks like your best pick is to use that of table a.

So, consider:

select *
from master_tbl m
left join tbla a on a.id = m.id
left join tblb b on b.id = m.id and b.desc = a.desc
left join tblc c on c.id = m.id and c.desc = a.desc
left join tbld d on d.id = m.id and d.desc = a.desc

If all descs are not available in tablea, we could switch to full joins. The logic is more complicated to follow, but that would look like:

select *
from master_tbl m
full join tbla a on a.id = m.id
full join tblb b on b.id = m.id and b.desc = a.desc
full join tblc c on c.id = m.id and c.desc = coalesce(a.desc, b.desc)
full join tbld d on d.id = m.id and d.desc = coalesce(a.desc, b.desc, c.desc)

Upvotes: 1

Related Questions