如何连接三个表并获得基于外键的布尔值

Question

我有一个包含销售人员和地区的简单数据库。一个地区只能有一个负责的销售人员(Districts表中的ResponsibleSalesmanId列(，但可以有多个次级销售人员(显示在SecondaryDistrictSalesmen表中(。

这是我的数据库模式：

CREATE TABLE [dbo].[Salesmen]
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY(1,1), 
[FirstName] NVARCHAR(50) NOT NULL, 
[LastName] NVARCHAR(50) NOT NULL, 
)
CREATE TABLE [dbo].[Districts]
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY(1,1), 
[Name] NVARCHAR(50) NOT NULL, 
[ResponsibleSalesmanId] INT NOT NULL, 
CONSTRAINT [FK_Districts_Salesmen] FOREIGN KEY ([ResponsibleSalesmanId]) REFERENCES [Salesmen]([Id])
)
CREATE TABLE [dbo].[SecondaryDistrictSalesmen]
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY(1,1), 
[DistrictId] INT NOT NULL, 
[SalesmanId] INT NOT NULL, 
CONSTRAINT [FK_SecondaryDistrictSalesmen_Districts] FOREIGN KEY ([DistrictId]) REFERENCES [Districts]([Id]), 
CONSTRAINT [FK_SecondaryDistrictSalesmen_Salesmen] FOREIGN KEY ([SalesmanId]) REFERENCES [Salesmen]([Id]) 
)

我想从这个数据库输出的是一个表，其中包含数据库中的所有销售人员以及他们与特定District(由WHERE[District].[Id]=x指定(的关系。

我正在考虑在输出结果中添加两列：IsResponsible和IsSecondary。如果每列中给定的销售人员负责输入District，则IsResponsible应包含1(如果不是，则为0(，IsSecondary列也应包含1。

我怎样才能达到这个结果？

Answer 1

嗯。看起来像union all:

select id as DistrictId, ResponsibleSalespersonId as SalespersonId, 1 as IsResponsbile, 0 as IsSecondary
from Districts
where id = @X
union all
select DistrictId, SalespersonId, 0, 1
from SecondaryDistrictSalesperson
where DistrictId = @X;

编辑：

我最初误解了这个问题。我以为你只想要某个地区的销售人员。假设一个销售人员只出现在两个表中一次，那么：

select s.*,
(case when d.ResponsibleSalespersonId is not null then 1 else 0 end) as IsResponsble,
(case when sds.SalespersonId is not null then 1 else 0 end) as IsSecondary,
from salesperson s left join
districts d
on d.ResponsibleSalespersonId = s.id and
d.districtid = @distictid left join
SecondaryDistrictSalesperson sds
on sds.SalespersonnId = s.id and
sds.districtid = @districtid;

Answer 2

交叉加入所有地区的所有销售人员。因此，您已经看到了整个结果集以及谁是主要的销售人员。为了判断销售人员是否是次要的，可以从外部连接第三个表，也可以使用exists子句。

select
s.id, s.firstname, s.lastname,
d.id, d.name,
case when s.salesmanid = d.responsiblesalesmanid then 1 else 0 end as is_primary,
case when sds.salesmanid is not null then 1 else 0 end as is_secondary
from salesmen s
cross join districts d
left join secondarydistrictsalesmen sds on sds.salesmanid = s.salesmanid
and sds.districtid = d.districtid
order by s.id, d.id;