表头和多个明细表查询问题

Question

我有一个标题表(header(和多个详细信息表(detail_aa、detail_ab等(。

create table header (header_id int primary key, header_name varchar(100));
create table detail_aa (detail_aa_id int primary key, detail_aa_name varchar(100), header_id int foreign key references header(header_id));
create table detail_ab (detail_ab_id int primary key, detail_ab_name varchar(100), header_id int foreign key references header(header_id));

注意：一个特定的header_id只能进入一个详细信息表。

因此，例如，如果我们在header表中有一行header_id=1，那么

• detail_aa表将有一行header_id=1或
• detail_ab表将有一行header_id=1或
• 任何详细信息表都不会有header_id=1的行

不可能detail_aa和detail_ab都有header_id=1的行。

示例：

表：标题

header_id header_name
--------- -----------
1         Master_1
2         Master_2
3         Master_3

表：detail_aa

detail_aa_id detail_aa_name header_id
------------ -------------- ---------
101          Detail_aa_101  1

表：detail_ab

detail_ab_id detail_ab_name header_id
------------ -------------- ---------
45           Detail_ab_45   2

我想写一个查询，显示标题表中的所有header_id、header_name和详细信息表中的相应数据，如果没有链接，则为null：

header_id header_name detail_table_name detail_id detail_name
--------- ----------- ----------------- --------- -------------
1         Master_1    detail_aa         101       Detail_aa_101
2         Master_2    detail_ab         45        Detail_ab_45
3         Master_3    null              null      null

我该怎么做？

Answer 1

我会先合并这两个细节表，然后使用左联接来获取所有标头，如果存在，则使用细节。

With Detail as (
SELECT 'detail_aa' as detail_table_name, detail_aa_name as detail_name, Detail_aa_id as Detail_ID
UNION 
SELECT 'detail_bb' as detail_table_name, detail_bb_name as detail_name, Detail_bb_id as Detail_ID)
SELECT H.Header_Id, H.Header_Name, D.*
FROM Header H
LEFT JOIN Detail D
on H.Header_ID = D.Header_ID

Answer 2

使用left join:

select h.*,
(case when da.header_id is not null
then 'detail_aa'
else 'detail_bb'
end),
coalesce(da.detail_id, db.detail_id) as detail_id
coalesce(da.detail_name, db.detail_name) as detail_name
from header h left join
detail_aa da
on h.header_id = da.header_id left join
detail_bb db
on h.header_id = db.header_id;