代码之家 › 专栏 › 技术社区 › Alex

SQL Server:两个表关系上的左外联接

left-join sql-server

Alex · 技术社区 · 5 年前

我需要创建一些SQL,通过连接这3个表,它将始终以尽可能多的信息传递结果。最佳结果-所有3个表都已连接。中等结果-至少选择了一些主键(或两者)。最坏结果所有列都为空。

只有当我加入第一张桌子时,它才对我有用。连接第二个会产生错误。我应该用什么代替?作为SQL语句?

CREATE TABLE [AGENCIES]
(
      [AGENCY_NAME] [CHAR](9), 
      id INT IDENTITY(1,1) NOT NULL PRIMARY KEY 
);

CREATE TABLE [PERSONS]
(
      [NAME] [CHAR](9), 
      id INT IDENTITY(1,1) NOT NULL PRIMARY KEY 
);

CREATE TABLE [AGENCY_PERSON]
(
     agency_id INT FOREIGN KEY REFERENCES agencies(id),
     person_id INT FOREIGN KEY REFERENCES persons(id),
     [TITLE] [CHAR](9) NULL, 
     id INT IDENTITY(1,1) NOT NULL PRIMARY KEY 
);

INSERT INTO agencies (AGENCY_NAME) 
VALUES ('AgencyOne'), ('AgencyTwo'), ('Agency3');

INSERT INTO persons (name) 
VALUES ('PersonOne'), ('PersonTwo'), ('Person3');

INSERT INTO AGENCY_PERSON (agency_id, person_id, title) 
VALUES (1, 1, 'TitleOne'), (1, 2, 'TitleTwo');

SELECT * FROM AGENCY_PERSON;

-- works fine for one primary table
SELECT [AGENCY_NAME], [TITLE] 
FROM agencies
LEFT OUTER JOIN [AGENCY_PERSON] ON [AGENCY_PERSON].agency_id = agencies.id
WHERE [AGENCY_NAME] = 'AgencyOne';

-- error for two primary tables: Msg 4104 - The multi-part identifier "agencies.id" could not be bound.

SELECT [AGENCY_NAME], [TITLE], persons.name 
FROM agencies, persons
LEFT OUTER JOIN [AGENCY_PERSON] ON [AGENCY_PERSON].agency_id = agencies.id 
                                AND [AGENCY_PERSON].person_id = persons.id
WHERE [AGENCY_NAME] = 'AgencyOne';

-- select ? 'AgencyOne' - all records exist
-- AgencyOne, TitleOne, PersonOne

-- select ? 'TitleTwo' - both records on primary tables exist, but no in join table
-- AgencyOne, TitleTwo, NULL

-- select ? 'Agency3' - one of primary tables exist
-- Agency3, NULL, NULL

-- select ? 'Title3' - one of primary tables exist
-- NULL, Title3, NULL

-- select ? 'AgencyX' - nothing exists
-- NULL, NULL, NULL

1 回复 | 直到 5 年前

forpas 5 年前

如果不使用旧样式(交叉)联接,则代码将正常工作:

from agencies, persons

select a.[AGENCY_NAME], ap.[TITLE], p.name 
from agencies as a cross join persons as p
left outer join [AGENCY_PERSON] as ap 
on ap.agency_id = a.id and ap.person_id = p.id
where a.[AGENCY_NAME] = 'AgencyOne';

我为所有涉及的表使用了别名,并用它们所属表的别名限定了所有列。

结果:

> AGENCY_NAME | TITLE     | name     
> :---------- | :-------- | :--------
> AgencyOne   | TitleOne  | PersonOne
> AgencyOne   | TitleTwo  | PersonTwo
> AgencyOne   | null      | Person3

我不确定这是否是您想要的输出,但我相信您现在看到了如何连接所有3个表。

select a.[AGENCY_NAME], ap.[TITLE], p.name 
from [AGENCY_PERSON] as ap
inner join agencies as a on ap.agency_id = a.id
inner join persons as p on ap.person_id = p.id
where a.[AGENCY_NAME] = 'AgencyOne';

结果:

> AGENCY_NAME | TITLE     | name     
> :---------- | :-------- | :--------
> AgencyOne   | TitleOne  | PersonOne
> AgencyOne   | TitleTwo  | PersonTwo

见 demo