不能在SQL Server 2008上绑定多部分标识符 [英] The multi-part identifier could not be bound on SQL Server 2008
问题描述
我有2张桌子
requests
(ID
,company_id
,amount
)
companies
(ID
,name
)
受FK约束(requests.company_id
-> companies.id
)
requests.company
可以是NULL
我需要获取所有请求,并用相应的公司name
替换company_id
,如果未指定公司,则将其留空.
I need to get all requests and replace company_id
with appropriated company name
or left it blank if no company was specified.
我有下一个查询:
SELECT R.[ID], C.[name] AS [company], R.[amount], ...
FROM [requests] AS R, [companies] AS C, ...
WHERE R.[company_id] = C.[ID]
并且可以正常工作,直到company
字段为NULL.
and it's working fine until a NULL into 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
但是得到了
无法绑定多部分标识符"R.company_id"
The multi-part identifier "R.company_id" could not be bound
和ON
子句移动中的字段上的相同错误.我在做什么错了?
And the same errors on fields in ON
clause shifting. What am I doing wrong?
推荐答案
您显示的代码示例带有省略号,并且我相信是引起问题的椭圆中的原因.
The code example you showed had ellipses and I believe it is what is in the ellipses that are causing the trouble.
您有:
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和...分隔开逗号的逗号,并且[eXample]在我的X中看不到.但是,以下方法确实起作用:
In other words the mixing of pre-ANSI 92 inner join syntax with ANSI 92 outer join syntax. Testing on SQL Server 2005, it appears that the alias R for requests is not seen past the comma that separates R from ... in your example, and [eXample] as X in mine. The following however did work:
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
或
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连接语法:
or my favorite, because I like ANSI 92 join syntax:
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
这篇关于不能在SQL Server 2008上绑定多部分标识符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!