使用join子句和多个ON组合来自多个表的值 [英] Combining values from multiple tables using join clause with multiple ON

问题描述

我想使用LEFT JOIN子句合并所有相关数据，但是如果一个表中没有与其他表匹配的记录，它将不会显示.您能检查一下我的查询吗，似乎丢失了或完全弄乱了.这是我的查询.

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

推荐答案

此方法使用 UNION ALL 将名为表的字母(tbla，tblb，tblc，tbld)组合为 CTE，常用表表达式.然后，组合表将按ID，[desc]进行汇总，并跨登录列进行交叉表化(或透视).然后将透视结果 LEFT JOIN '添加到master_tbl.像这样的东西.

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;

这篇关于使用join子句和多个ON组合来自多个表的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！