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 “Multi Part Identifier Could not be Bound” Error Message
Let’s see below, a relevant example that reproduces the above error message.
Consider two tables; table Employee and table Address.
Employee table:
CREATE TABLE [dbo].[Employee]( [id] [int] NOT NULL, [name] [varchar](50) NULL, [age] [int] NULL ) ON [PRIMARY]
Address table
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! Enroll to our 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.
Watch video: The Multi Part Identifier Could not be Bound – How to Resolve in SQL Server
Featured Online Courses:
- AI Essentials: A Beginner’s Guide to Artificial Intelligence
- SQL Server 2022: What’s New – New and Enhanced Features
- Working with Python on Windows and SQL Server Databases
- Introduction to Azure Database for MySQL
- Boost SQL Server Database Performance with In-Memory OLTP
- Introduction to Azure SQL Database for Beginners
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- Data Management for Beginners – Main Principles
- 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
- The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered – How to Resolve it
- Introduction to Azure Database for MySQL (Course Preview)
- SQL Server replication requires the actual server name to make a connection to the server – How to Resolve it
- Issue Adding Node to a SQL Server Failover Cluster – Greyed Out Service Account – How to Resolve
- Resolve SQL Server CTE Error – Incorrect syntax near ‘)’.
- SQL Server is Terminating Because of Fatal Exception 80000003 – How to Troubleshoot
- … more SQL Server troubleshooting articles
Subscribe to our newsletter and stay up to date!
Check out our latest software releases!
Check our eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
© SQLNetHub
Artemakis Artemiou is a seasoned Senior Database and AI/Automation Architect with over 20 years of expertise in the IT industry. As a Certified Database, Cloud, and AI professional, he has been recognized as a thought leader, earning the prestigious Microsoft Data Platform MVP title for nine consecutive years (2009-2018). Driven by a passion for simplifying complex topics, Artemakis shares his expertise through articles, online courses, and speaking engagements. He empowers professionals around the globe to excel in Databases, Cloud, AI, Automation, and Software Development. Committed to innovation and education, Artemakis strives to make technology accessible and impactful for everyone.
Simply Forgeting the "FROM" part of a SQL Expression will give you the same error. I pulled half my hair out finding this simple mistake after 17 years of writing SQL statements….Ahhh!
Yes, that's true! 🙂
Well, I am quite sure that everyone of us does such mistakes sometimes!
The good thing is that in the end, finally we find and correct the mistake!
But don't forget the WHERE clause, because then you will be bald before you are 30!
Wrongly refering a column also gives same error.
e.g.
Select * from EMP t
where e.EmployeeID = 5
would give same error.
Hi Tahir,
Well said!
Make sure you have spelled the table name correctly. Misspelling the table name as in "Student.First_Name" if the table name is "Students" (with an s at the end) will also cause this error.
Hello, I am learning Sql and I am stuck doing this stored procedure (see below). Can someone direct me on what I am doing wrong? I am trying to write a code that allows a user to enter business information from two separate table called Business and Employer when "HaveBusiness"? which is a table in another table called person is yes (or = 1) and then reset to Zero (or no) after the insert statements:
CREATE PROCEDURE [dbo] .[BusinessInfo]
@BusinessId int,
@PersonId int,
@BusinessName nvarchar (50),
@BizAddr nvarchar (50),
@BizCity nvarchar (10) = NULL,
@BizState nvarchar,
@BizCountryId nvarchar,
@BizFieldId int,
@BizPhone int,
@BizEmail nvarchar (30),
@BizWebsite nvarchar (50) = NULL,
@BizFax int = 0,
@DateBizStarted date,
@AboutBiz nvarchar (75)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
If dbo.person.HaveBusiness = 1
Insert into dbo.Business (BusinessName, BizAddr, BizCity, BizState, BizCountryId, BizFieldId)
Values (@BusinessName, @BizAddr, @BizCity, @BizState, @BizCountryId, @BizFieldId)
Insert into dbo.Employer (BizPhone, BizEmail, BizWebsite, BizFax, DateBizStarted, AboutBiz)
Values (@BizPhone, @BizEmail, @BizWebsite, @BizFax, @DateBizStarted, @AboutBiz)
COMMIT TRANSACTION
Update HaveBusiness
SET HaveBusiness = 0;
thanks! Simple and objective