1

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

GMB
  • 216,147
  • 25
  • 84
  • 135
Thirdy Leon
  • 59
  • 1
  • 7

2 Answers2

1

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)
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Hi @GMB thank you for the query, even the records are showing but if the tbla has no record matched to desc the the rest of the logins are not showing, also the other login from tblb repeats on the other record... – Thirdy Leon Oct 23 '20 at 23:11
  • @ThirdyLeon: welcome! I am suprised that you accepted the other answer, that is more complex, and probably less efficient. – GMB Oct 24 '20 at 00:46
  • because that's the only query fits to what I'm looking for. The second query gives me the same result with the first one but with extra null records. – Thirdy Leon Oct 24 '20 at 01:15
  • Hi @GMB, in my actual table I have a lot of fields which I did not mention earlier for the sake of sample only, I need to show additional fields for tbla=8 fields, tblb=6 fields, tblc=7 fields and tbld=8 fields, how can I add them? I've tried to add them in pvt_cte and in group by it works but some values went to somewhere else not to supposed related records... – Thirdy Leon Oct 24 '20 at 01:47
0

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;
SteveC
  • 5,955
  • 2
  • 11
  • 24