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

inner-join, sql-server, sql-server-2008, t-sql

Solution

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

Problem

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?

Original source