There are cases where a SQL Server database developer might get an error message similar to: “The multi part identifier could not be bound“. This happens because of the way the database developer handles table scopes within the query. Read the article below, in order to better understand this error, and see how easy is to resolve it via a simple example.
Reproducing the Error Message
Let’s see below, a relevant example that reproduces the above error message.
Consider two tables; table Employee and table Address.
CREATE TABLE [dbo].[Employee]( [id] [int] NOT NULL, [name] [varchar](50) NULL, [age] [int] NULL ) ON [PRIMARY]
CREATE TABLE [dbo].[address]( [empid] [int] NOT NULL, [street] [varchar](50) NULL, [city] [varchar](50) NULL, [country] [varchar](50) NULL ) ON [PRIMARY]
Let’s say we want to write a query returning all the employees and their country of residence sorted by the latter alphabetically.
A suggested query would be the following:
SELECT emp.name AS EmployeeName , addr.country AS EmployeeCountry FROM [Employee] emp INNER JOIN [Address] addr ON emp.id = addr.empID ORDER BY addr.country ASC;
Indeed, the above query works fine.
Though if someone tried to get the employees’ country using a subquery like this:
SELECT emp.name AS EmployeeName , ( SELECT addr.country FROM [Address] addr WHERE addr.empID = emp.id ) AS EmployeeCountry FROM [Employee] emp ORDER BY addr.country ASC; GO
… then he/she would end up with the following error:
The multi-part identifier “addr.country” could not be bound.
Explaining and Resolving the Error
The problem in the above T-SQL Statement is that even though we used “addr” as a table alias in the subquery, we are not syntactically allowed to use it outside the scope of the subquery which, in this example, is in the order by clause. Though the opposite is possible, that is to reference a table/alias of an outer query within an internal query (subquery). That is why in our subquery we are able to reference the emp.id table/column.
For eliminating the above error and keep on using the subquery, the correct code for this case would be:
SELECT emp.name AS EmployeeName , ( SELECT addr.country FROM [Address] addr WHERE addr.empID = emp.id ) AS EmployeeCountry FROM [Employee] emp ORDER BY EmployeeCountry; GO
Analysis and Discussion
Even though in this example the problem was obvious, in many cases where we develop some really large and complex queries along with subqueries, we might end up consuming valuable time for resolving such issues 🙂
To this end we should always be careful when using subqueries in our T-SQL statements and always keep in mind that subqueries can only provide their results to their outer queries and not references to the subqueries’ tables.
A future post will thoroughly explain the usage of subqueries in SQL Server.
Check some other related error messages and ways to resolve them:
- Error converting data type varchar to float
- Operating System Error 170 (Requested Resource is in use)
- Installing SQL Server 2016 on Windows Server 2012 R2: Rule KB2919355 failed
- A connection was successfully established with the server, but then an error occurred during the login process.
- There is insufficient system memory in resource pool ‘internal’ to run this query.
- Argument data type ntext is invalid for argument …
- Could not load file or assembly ‘Microsoft.SqlServer.Smo, Version=10.0.0.0, …
- … more SQL Server troubleshooting articles
Subscribe to our newsletter and stay up to date!
Check out our latest software releases!
Check out Artemakis’s eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)