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

I have 2 tables

我有2张桌子

requests (ID, company_id, amount)

请求(ID,company_id,金额)

companies (ID, name)

公司(ID,名称)

with FK constraint (requests.company_id -> companies.id)

与FK约束(requests.company_id - > companies.id)

requests.company can be NULL

requests.company可以为NULL

I need to get all requests and replace company_id with appropriated company name or left it blank if no company was specified.

我需要获取所有请求并用适当的公司名称替换company_id,或者如果没有指定公司则将其留空。

I have next query:

我有下一个查询:

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

and it's working fine until a NULL into company field.

并且它工作正常,直到NULL进入公司字段。

I tried to do next:

我试着做下一个:

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

The multi-part identifier "R.company_id" could not be bound

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

And the same errors on fields in ON clause shifting. What am I doing wrong?

和ON子句移位中的字段相同的错误。我究竟做错了什么?

3 个解决方案

#1

The code example you showed had ellipses and I believe it is what is in the ellipses that are causing the trouble.

你展示的代码示例有省略号,我相信它是导致麻烦的省略号中的内容。

You have:

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

Let's say that is something like:

让我们说这就是:

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

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:

换句话说,将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

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.

or my favorite, because I like ANSI 92 join syntax:

或者我最喜欢的,因为我喜欢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

#2

I think you want:

我想你想要:

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

EDIT: See comments, the left join is needed ...

编辑:看到评论,需要左连接...

It also appears to me that there's no need for the left join, so you can re-write as:

在我看来,没有必要左连接,所以你可以重写为:

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

#3

Changing the table order in comma makes it work in sql server 2005,2008 and 2012

以逗号更改表顺序使其在sql server 2005,2008和2012中可用

e.g

Let's say you have a query like below which will fail in sql 2005 and above:

假设您有一个如下所示的查询,它将在sql 2005及更高版本中失败:

SELECT t1.*,t2.*, t3.*
FROM table1 AS t1, table2 as t2 
LEFT OUTER JOIN table3 AS t3
ON t1.id = t3.id
WHERE t1.id = t2.id

The query will work in sql 2005 and above if you rewrite the query and change the order of the table in the commas before the join. e.g

如果您重写查询并在连接之前更改逗号中表的顺序,则查询将在sql 2005及更高版本中运行。例如

SELECT t1.*,t2.*, t3.*
FROM table2 as t2, table1 as t1 
LEFT OUTER JOIN table3 AS t3
ON t1.id = t3.id
WHERE t1.id = t2.id

#1