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

I have 2 tables

requests (ID, company_id, amount)

  • php sqlsrv_query stored procedure with naming parameters
  • Use a specific database and table in MSSQL (Visual Studio)
  • How to count the most frequent CloseReasonTypes per posts in the dataexplorer?
  • Sum the Attendance Hours by Category and then Group by 'Week of'
  • How to encrypt numeric column in Sql Server 2008?
  • How to use ROW_NUMBER() in UPDATE clause?
  • 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?

  • How do I raise an error in an Execute Sql Task in Integration Services?
  • Protect sensitive information from the DBA in SQL Server 2008
  • Manage SSDT project file properly with version control (*.sqlproj)
  • Select MAX with PHP/SQL Server with ADODB
  • OR Operator Short-circuit in SQL Server
  • SQL Server 2012 Adding a computed column to a view from another table with different data types
  • 3 Solutions collect form web for “The multi-part identifier could not be bound on SQL Server 2008”

    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
    

    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
    

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.