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.
Learn more tips like this! Check our new Online Course!
Check our online course titled “Essential SQL Server Development Tips for SQL Developers” (special limited-time discount included in link).
Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!
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.
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals (SQL Database for Beginners)
- Essential SQL Server Development Tips for SQL Developers (New)
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners: Windows Forms (C#)
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database
- SQL Server 2019: What’s New
- Entity Framework: Getting Started (Complete Beginners Guide)
- How to Import and Export Data in SQL Server
- Get Started with SQL Server in 30 Minutes
- A Guide on How to Start and Monetize a Successful Blog
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, …
- Fix: VS Shell Installation has Failed with Exit Code 1638
- … 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)
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA).