无法在 SQL Server 2008 上绑定多部分标识符

我有2张桌子

requests (ID, company_id, amount)

companies (ID, name)

具有 FK 约束（requests.company_id -> companies.id)

requests.company can be NULL

我需要获取所有请求并替换company_id与适当的公司name如果未指定公司，则将其留空。

我有下一个查询：

SELECT R.[ID], C.[name] AS [company], R.[amount], ...
FROM [requests] AS R, [companies] AS C, ...
WHERE R.[company_id] = C.[ID]

它工作正常，直到 NULL 进入company field.

我尝试下一步：

SELECT R.[ID], C.[name] AS [company], ...
FROM [requests] AS R, ...
LEFT OUTER JOIN [companies] AS C
ON R.[company_id] = S.ID

But got

无法绑定多部分标识符“R.company_id”

以及字段中的相同错误ON条款转移。我究竟做错了什么？

您显示的代码示例有省略号，我相信正是省略号中的内容造成了麻烦。

你有：

SELECT R.[ID], C.[name] AS [company], ...
FROM [requests] AS R, ...
LEFT OUTER JOIN [companies] AS C
ON R.[company_id] = S.ID

假设是这样的：

SELECT R.[ID], C.[name] AS [company], X.Field
FROM [requests] AS R, [eXample] as X 
LEFT OUTER JOIN [companies] AS C
ON R.[company_id] = S.ID
WHERE X.[request_id] = R.ID

换句话说，ANSI 92 之前的内连接语法与 ANSI 92 外连接语法的混合。在 SQL Server 2005 上进行测试，在您的示例中，似乎在将 R 与 ... 分隔开的逗号后面看不到请求的别名 R，而在我的示例中，[eXample] 作为 X。然而，以下方法确实有效：

SELECT R.[ID], C.[name] AS [company], X.Field
FROM [eXample] as X, [requests] AS R 
-- Requests and companies on the same side of the comma
LEFT OUTER JOIN [companies] AS C
ON R.[company_id] = S.ID
WHERE X.[request_id] = R.ID

or

SELECT R.[ID], C.[name] AS [company], X.Field
FROM [requests] AS R LEFT OUTER JOIN [companies] AS C
    ON R.[company_id] = S.ID, [eXample] as X 
WHERE X.[request_id] = R.ID
-- Yuck, I would hate to find this. Not at all sure from reading
-- the code how it would work.

或者我最喜欢的，因为我喜欢 ANSI 92 连接语法：

SELECT R.[ID], C.[name] AS [company], X.Field
FROM [requests] AS R
INNER JOIN [eXample] as X ON X.[request_id] = R.ID
LEFT OUTER JOIN [companies] AS C ON R.[company_id] = S.ID