The multi-part identifier could not be bound on SQL Server 2008

Question

I have 2 tables

requests (ID, company_id, amount)

companies (ID, name)

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

requests.company can be NULL

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

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.

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

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

Solution 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:

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.

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

Solution 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

Solution 3

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

e.g

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

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

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

Comments

[Shannon Severance]

He does need the left join. Requests.Company_ID can be null. Without an outer join, requests where Company_ID is null would be dropped from the results.

[Jamie Ide]

@Shannon, you're right, I missed this: or left it blank if no company was specified.

[Jamie Ide]

Where is the table aliased as "S" so that the constraint "= S.ID" works?

[Shannon Severance]

S.ID was in the OP, probably something within the ellipses. ??? Since it helped the OP, I don't want to edit, because I'm afraid I'd just butcher the explanation. It was hard working with the missing code.