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!
(Lifetime Access/ Live Demos / Downloadable Resources and more!) Enroll from $14.99
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:
- SQL Server 2022: What’s New – New and Enhanced Features [New]
- Data Management for Beginners – Main Principles
- Introduction to Azure Database for MySQL
- Working with Python on Windows and SQL Server Databases
- 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
- A Guide on How to Start and Monetize a Successful Blog
- Data Management for Beginners – Main Principles
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: 
(8 votes, average: 5.00 out of 5)
Loading…
Reference: SQLNetHub.com (https://www.sqlnethub.com)
© SQLNetHub
The Multi Part Identifier Could not be Bound
Click to Tweet
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and {essentialDevTips.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). Moreover, Artemakis teaches on Udemy, you can check his courses here.
Views: 12,773
- Remove From My Forums
-
Question
-
Hello,
I am having trouble exceuting the following query:
«IF @sync_initialized = 0 SELECT dbo.tOrder.[OrderNo], dbo.tOrder.[UserId], [ShipperName], [OpenClosedSts] FROM dbo.tOrder LEFT OUTER JOIN CHANGETABLE(CHANGES dbo.tOrder,
@sync_last_received_anchor) CT ON CT.[OrderNo] = dbo.tOrder.[OrderNo] AND CT.[UserId] = dbo.tOrder.[UserId] WHERE dbo.tOrder.[OpenClosedSts]=@OpenClosedSts AND CT.UserId=@UserId AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <>
@sync_client_id_binary) ELSE BEGIN SELECT dbo.tOrder.[OrderNo], dbo.tOrder.[UserId], [ShipperName], [OpenClosedSts] FROM dbo.tOrder JOIN CHANGETABLE(CHANGES dbo.tOrder, @sync_last_received_anchor) CT ON CT.[OrderNo] = dbo.tOrder.[OrderNo] AND CT.[UserId]
= dbo.tOrder.[UserId] WHERE dbo.tOrder.[OpenClosedSts]=@OpenClosedSts AND CT.UserId=@UserId AND (CT.SYS_CHANGE_OPERATION = ‘I’ AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT
<> @sync_client_id_binary));I am getting the following error message:
The multi-part identifier «dbo.tOrder.OpenClosedSts» could not be bound.Sorry for the very long statement. This statement is working if this particular condition is removed:
dbo.tOrder.[OpenClosedSts]=@OpenClosedStsThank you,
Divya
Answers
-
I would think that what Naomi posted should work, but I’m trying to figure out why it didn’t work originally.
Is it possible that you have some unprintable (invisible) character between your WHERE and the reference to OpenClosedSts? Perhaps it’s interpreting that character along with the alias to come up with ?a.OpenClosedSts or ?dbo.tOrder.OpenClosedSts (where
? is the offending character).If you delete the WHERE clause and (carefully) re-type it from scratch, does it make any difference?
—Brad (My Blog)
-
Proposed as answer by
Wednesday, July 20, 2011 9:16 PM
-
Marked as answer by
Kalman Toth
Wednesday, July 27, 2011 12:53 AM
-
Proposed as answer by
- Remove From My Forums
-
Вопрос
-
Hi I am receiving the following error: «The multi-part identifier «v_Dept.depts» could not be bound» when running the following SQL code:
select v_Dept.depts AS [Department], [1] AS January, [2] AS Febrary, [3] AS March, [4] AS April, [5] AS May, [6] AS June, [7] AS July, [8] AS August, [9] AS September, [10] AS October, [11] AS November, [12] AS December from ( SELECT datepart(mm, cmtrp.mnth) AS my_month, v_Dept.depts AS [Department] FROM v_Dept RIGHT OUTER JOIN cmatp ON v_Dept.ds_reckey = camatp.mg_depart LEFT OUTER JOIN cclient ON camatp.mg_client = caclient.cl_accode WHERE (caclient.cl_clgrp IN ('YOUR', 'NEWHA', 'EWM')) AND (CAST(cmatp.datopn AS datetime) BETWEEN CAST('2011-05-01 00:00:00.000' AS datetime) AND CAST('2014-04-30 00:00:00.000' AS datetime)) OR (CAST(cmatp.datopn AS datetime) BETWEEN CAST('2011-05-01 00:00:00.000' AS datetime) AND CAST('2014-04-30 00:00:00.000' AS datetime)) AND (client.cl_code IN ('00000075', '00071667', '00001204')) ) AS t PIVOT ( COUNT([Department]) FOR my_month IN([1], [2], [3], [4], [5],[6],[7],[8],[9],[10],[11],[12]) ) pIt is something to do with how I am structuring my SQL code but I can’t for the life of me figure out how I rewrite this despite me knowing I have done this for previous reports!
Can somebody please help? Thanks, Dan
Ответы
-
Please re-read all my replies one more time carefully.
Once again.
1. Start with the query you currently have as subquery (the one you aliased as t). Make sure it works and returns Department, my_Month, someOtherColumn.
2. Once you have that query working for you correctly (no typos), turn this query into CTE (common table expression for simplicity), e.g.
;with cte as (here goes the working query)
select * from cte — one more check
The ; before cte is important and should not be omitted.
3. If you have select * from cte working, then proceed with your final query that should be
select * from cte PIVOT (count(SomeOtherColumn)
for my_month in ([January], [February], etc.)) pvt
——————
I suggest to get my_month using the name of the month, e.g. DATENAME function, this way you would not need changing [1], [2], etc. into English month names and can simply use select *
—————
That’s all what is needed here and if you can follow this simple plan, you’ll get your PIVOT working.
-
Изменено
20 июля 2015 г. 15:32
-
Помечено в качестве ответа
Dan Rooney
21 июля 2015 г. 9:45
-
Изменено
-
Please re-read all my replies one more time carefully.
Once again.
1. Start with the query you currently have as subquery (the one you aliased as t). Make sure it works and returns Department, my_Month, someOtherColumn.
2. Once you have that query working for you correctly (no typos), turn this query into CTE (common table expression for simplicity), e.g.
;with cte as (here goes the working query)
select * from cte — one more check
The ; before cte is important and should not be omitted.
3. If you have select * from cte working, then proceed with your final query that should be
select * from cte PIVOT (count(SomeOtherColumn)
for my_month in ([January], [February], etc.)) pvt
——————
I suggest to get my_month using the name of the month, e.g. DATENAME function, this way you would not need changing [1], [2], etc. into English month names and can simply use select *
—————
That’s all what is needed here and if you can follow this simple plan, you’ll get your PIVOT working.
Thank you, thank you, thank you Naomi!! This is pretty much my working query for those wanting to know…
;with cte as
(
SELECT datepart(mm, camgrp.datopn) AS my_month, v_Dept.depts AS [Department], another_column
FROM v_Dept
RIGHT OUTER JOIN camgrp ON v_Department.ds_reckey = camgrp.mg_depart
LEFT OUTER JOIN client ON camgrp.mg_cli = client.cl_code
WHERE (client.clgrp IN (‘YOUR’, ‘EWM’))
)
SELECT * FROM ctePIVOT (
COUNT([another_column])
FOR my_month IN([1], [2], [3], [4], [5],[6],[7],[8],[9],[10],[11],[12])
) pWHERE [Department] IS NOT NULL
-
Помечено в качестве ответа
Eric__Zhang
27 июля 2015 г. 9:49
-
Помечено в качестве ответа
One of the most common errors that you might face in join statements is this error:
Msg 4104, Level 16, State 1, Line …
The multi-part identifier …… could not be bound.
The main reason for this error is that the source table cannot be found, for example if you have statement such as Table1.OrderDate, and then if you get error above, this means that Table1 cannot be found in the query. Sometimes you can see that source table exists in the query, but T-SQL cannot understand it, especially when you write join statements.
Consider join statement below:
select * from sales.SalesOrderHeader SH
left outer join sales.SalesOrderDetail SD
left outer join Production.Product P
on SD.ProductID=p.ProductID and SH.SalesOrderID=SD.SalesOrderID
With statement above you will get the error :
the reason is that SalesOrderHeader (SH) table is hidden from the third part of the join. and if you want to apply joining condition it will throw the error.
Resolution is:
put join condition of each join statement exactly after it. query below is showing fixed version of that statement:
select * from sales.SalesOrderHeader SH
left outer join sales.SalesOrderDetail SD
on SH.SalesOrderID=SD.SalesOrderID
left outer join Production.Product P
on SD.ProductID=p.ProductID
This statement used SH.SalesOrderID = SD.SalesOrderID right after the first join where the SH table is totally visible.
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
|
Home > SQL Server Error Messages > Msg 4104 — The multi-part identifier could not be bound. |
||
|
SQL Server Error Messages — Msg 4104 — The multi-part identifier could not be bound. |
||
To illustrate, given the following table structure: CREATE TABLE [dbo].[Employee] (
[EmployeeID] INT
[FullName] VARCHAR(100),
[ManagerID] INT
)
The following SELECT statement will generate the error: SELECT [Manager].[FullName] AS [ManagerName], [Emp].[EmployeeID], [Emp].[FullName] FROM [dbo].[Employee] [Emp] INNER JOIN [dbo].[Employee] [Mgr] ON [Emp].[ManagerID] = [Mgr].[EmployeeID] Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Manager.FullName" could not be bound. The error is encountered because there is no [Manager] table or alias defined in the FROM clause. Another way of getting the error is when an alias has been assigned to a table referenced in the FROM clause of a statement and the table is used as a prefix of a column instead of using the alias. To illustrate, here’s another way of getting the error: SELECT [Employee].[EmployeeID], [Emp].[FullName] FROM [dbo].[Employee] [Emp] Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Employee.EmployeeID" could not be bound. Since an alias has already been used for the [dbo].[Employee] table, the alias, in this case [Emp], should be used instead of the table name when used as a prefix in the column names. The error can also happen not just as part of the column list in the SELECT statement but can also happen when the referenced column is in the JOIN clause, WHERE clause or the ORDER BY clause. -- As part of the JOIN clause SELECT [Mgr].[FullName] AS [ManagerName], [Emp].[EmployeeID], [Emp].[FullName] FROM [dbo].[Employee] [Emp] INNER JOIN [dbo].[Employee] [Mgr] ON [Emp].[ManagerID] = [Manager].[EmployeeID] -- As part of the ORDER BY clause SELECT [Mgr].[FullName] AS [ManagerName], [Emp].[EmployeeID], [Emp].[FullName] FROM [dbo].[Employee] [Emp] INNER JOIN [dbo].[Employee] [Mgr] ON [Emp].[ManagerID] = [Mgr].[EmployeeID] ORDER BY [Manager].[FullName] Solution / Workaround: This error can easily be avoided by making sure that the table or alias used when qualifying columns in the SELECT statement matches one of the tables or aliases specified in the FROM clause. In the first scenario, simply change the incorrect alias used in the SELECT statement. Changing the [Manager] prefix, which doesn’t exist in the FROM clause, to [Mgr] will solve the issue: SELECT [Mgr].[FullName] AS [ManagerName], [Emp].[EmployeeID], [Emp].[FullName] FROM [dbo].[Employee] [Emp] INNER JOIN [dbo].[Employee] [Mgr] ON [Emp].[ManagerID] = [Mgr].[EmployeeID] In the second scenario, since an alias has already been assigned to the table, use that alias instead of the table when prefixing columns from then on. Changing the [Employee] table prefix to the [Emp] alias will solve the issue: SELECT [Emp].[EmployeeID], [Emp].[FullName] FROM [dbo].[Employee] [Emp] |
||
| Related Articles : | ||