Ошибка the multi part identifier could not be bound

You are mixing implicit joins with explicit joins. That is allowed, but you need to be aware of how to do that properly.

The thing is, explicit joins (the ones that are implemented using the JOIN keyword) take precedence over implicit ones (the ‘comma’ joins, where the join condition is specified in the WHERE clause).

Here’s an outline of your query:

SELECT
  …
FROM a, b LEFT JOIN dkcd ON …
WHERE …

You are probably expecting it to behave like this:

SELECT
  …
FROM (a, b) LEFT JOIN dkcd ON …
WHERE …

that is, the combination of tables a and b is joined with the table dkcd. In fact, what’s happening is

SELECT
  …
FROM a, (b LEFT JOIN dkcd ON …)
WHERE …

that is, as you may already have understood, dkcd is joined specifically against b and only b, then the result of the join is combined with a and filtered further with the WHERE clause. In this case, any reference to a in the ON clause is invalid, a is unknown at that point. That is why you are getting the error message.

If I were you, I would probably try to rewrite this query, and one possible solution might be:

SELECT DISTINCT
  a.maxa,
  b.mahuyen,
  a.tenxa,
  b.tenhuyen,
  ISNULL(dkcd.tong, 0) AS tongdkcd
FROM phuongxa a
  INNER JOIN quanhuyen b ON LEFT(a.maxa, 2) = b.mahuyen
  LEFT OUTER JOIN (
    SELECT
      maxa,
      COUNT(*) AS tong
    FROM khaosat
    WHERE CONVERT(datetime, ngaylap, 103) BETWEEN 'Sep 1 2011' AND 'Sep 5 2011'
    GROUP BY maxa
  ) AS dkcd ON dkcd.maxa = a.maxa
WHERE a.maxa <> '99'
ORDER BY a.maxa

Here the tables a and b are joined first, then the result is joined to dkcd. Basically, this is the same query as yours, only using a different syntax for one of the joins, which makes a great difference: the reference a.maxa in the dkcd‘s join condition is now absolutely valid.

As @Aaron Bertrand has correctly noted, you should probably qualify maxa with a specific alias, probably a, in the ORDER BY clause.

Источник

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 $12.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: 1 Star 2 Stars 3 Stars 4 Stars 5 Stars (8 votes, average: 5.00 out of 5)

Loading…

Reference: SQLNetHub.com (https://www.sqlnethub.com)

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.

Источник

If you get an error telling you that the “The multi-part identifier could not be bound.”, it usually means that you’re prefixing one or more columns with either a table that isn’t included in your query, or an alias that you haven’t actually assigned to a table.

Fortunately, the error message shows you which multi-part identifier is causing the problem.

Example

Here’s an example to demonstrate how to get the error.

SELECT * FROM Cats
INNER JOIN Dogs d
ON c.CatName = d.DogName;

Result:

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "c.CatName" could not be bound.

In this example, I forget to include an alias for the Cats table.

The error will also occur if you try to reference the table name after you’ve already provided an alias.

Like this:

SELECT * FROM Cats c
INNER JOIN Dogs d
ON Cats.CatName = d.DogName;

Result:

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "Cats.CatName" could not be bound.

So in this case, I correctly assigned the alias, but in the ON clause I referenced the actual table name instead of the alias.

The Solution

So the solution is to ensure you’ve assigned all aliases that you might reference later in the query, and also to make sure you use those aliases instead of the table name directly.

So if you’re going to use aliases, like this:

SELECT * FROM Cats c
INNER JOIN Dogs d
ON c.CatName = d.DogName;

Or if you choose not to use aliases, like this:

SELECT * FROM Cats
INNER JOIN Dogs
ON Cats.CatName = Dogs.DogName;

Источник

The multi part identifier could not be bound is an SQL error that usually indicates that you are prefixing one or several columns with a table you have not included in the query.

Источник

Fortunately, the error message shows you which multi-part identifier is causing the problem.

Example

Here’s an example to demonstrate how to get the error.

SELECT * FROM Cats
INNER JOIN Dogs d
ON c.CatName = d.DogName;

Result:

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "c.CatName" could not be bound.

In this example, I forget to include an alias for the Cats table.

The error will also occur if you try to reference the table name after you’ve already provided an alias.

Like this:

SELECT * FROM Cats c
INNER JOIN Dogs d
ON Cats.CatName = d.DogName;

Result:

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "Cats.CatName" could not be bound.

So in this case, I correctly assigned the alias, but in the ON clause I referenced the actual table name instead of the alias.

The Solution

So the solution is to ensure you’ve assigned all aliases that you might reference later in the query, and also to make sure you use those aliases instead of the table name directly.

So if you’re going to use aliases, like this:

SELECT * FROM Cats c
INNER JOIN Dogs d
ON c.CatName = d.DogName;

Or if you choose not to use aliases, like this:

SELECT * FROM Cats
INNER JOIN Dogs
ON Cats.CatName = Dogs.DogName;

Источник

The multi part identifier could not be bound is an SQL error that usually indicates that you are prefixing one or several columns with a table you have not included in the query.

Also, you may try this error if you use an alias you have not assigned to a table. In this guide, you will learn the various causes of this error and how to solve the error. Read on to find out.

Contents

What Triggers the Multi Part Identifier Could Not Be Bound Error?
- – Popular Causes of This Error
- – Variations of This Error
- – Example 1: How To Trigger This Error Using Aliases
- – Example 2: Wrong Use of Dbo Schema
- – Example 3: Triggering the Error Using Join Statements
How To Solve the Error
- – Solution 1: Use Assigned Aliases
- – Solution 2: The Correct Use of Schema
- – Solution 3: Using Joins Correctly
Conclusion

What Triggers the Multi Part Identifier Could Not Be Bound Error?

The main reason you are triggering this error in your SQL is that your code is unable to find the source table. For instance, if you have an SQL statement like Table0.OrderDate that triggers this error could indicate that Table0 is nowhere to be found.

In some cases, the table may exist but the SQL is unable to understand your query especially when you use join statements.

Therefore, you can trigger this error if you mix explicit and implicit joins in your query. This is usually allowed, however, you must understand how to accomplish this properly. The thing with explicit joins is that they have higher priority than implicit joins.

Just to refresh your understanding, explicit joins are the ones that you implement using the JOIN keyword. On the other hand, implicit joins are the ones where you specify the join condition using the WHERE clause.

In nutshell, the error “The multi-part identifier could not be bound” typically occurs when you are executing a SQL query, and the query is unable to resolve a multi-part identifier. A multi-part identifier is an identifier in a SQL query that consists of two or more parts, such as a table name and a column name.

– Popular Causes of This Error

This error can be caused by a variety of issues, such as

Typos or spelling errors: If you do not spell the multi-part identifier correctly, you will end up triggering this error.
Incorrect table or column names: Cross-check the table and column names you are using in your query to establish if they are correct and that they exist in the database.
Incorrect table or column alias: If you are using table or column aliases in your query, make sure that they are correctly defined and that they are being used consistently throughout the query.
Incorrect table or column references: Make sure that you are referencing the correct tables and columns in your query.
Incorrect table or column permissions: It is advisable to ensure that you have the necessary permissions to access the tables and columns you are trying to use in your query.

Also, you will trigger the error if you pass a null value in your where clause in an SQL query, especially when using JDBC. This is misleading since when you do a quick online search, you will end up with something that indicates that your query is wrong.

However, in some cases, that may not be the case. That is a heads-up just in case you come across the same issue.

You may also trigger this error in an updated SQL query. If this happens, crosscheck the JOIN on your table in the field causing the error. It is possibly arising from the lack of JOIN, which will trigger the error in question because of an unknown field. The error you will get will look something like this the multi-part identifier could not be bound update.

Additionally, you will get this error if you use a field in your query that is not in the table. Suppose you refer to field1 in table1 but the field is in table2.

– Variations of This Error

C# the multi-part identifier could not be bound: The error “The multi-part identifier could not be bound” can occur in C# when a SQL query is unable to resolve a multi-part identifier.
Mssql the multi-part identifier could not be bound: This error can occur in Microsoft SQL Server when a SQL query is unable to resolve a multi-part identifier.
The multi-part identifier could not be bound where clause: The error can occur in a WHERE clause of a SQL query when the query is unable to resolve a multi-part identifier.
The multi-part identifier could not be bound sequelize: This error can occur in Sequelize, a popular Node.js ORM for working with databases, when a SQL query is unable to resolve a multi-part identifier.
The multi-part identifier could not be bound subquery: The error can occur in a subquery when the subquery is unable to resolve a multi-part identifier.
the multi-part identifier could not be bound outsystems: The error “The multi-part identifier could not be bound” can occur in OutSystems when a SQL query is unable to resolve a multi-part identifier.

– Example 1: How To Trigger This Error Using Aliases

Suppose you have a cat table and a dog table, and you want to show the names of all the cats and dogs. You can try to accomplish this as follows in an SQL query:

SELECT * FROM Cat c

INNER JOIN Dog d

ON Cat.CatName = d.DogName;

If you run this query, you will trigger this error. The reason is you correctly assign the Cat table an alias, but in the ON clause, you reference the actual name of the table rather than using the alias.

– Example 2: Wrong Use of Dbo Schema

If you wrongly use the schema(dbo) in your query, you are going to trigger this error. Suppose you write the following SQL server.

SELECT dbo.prod.name

FROM dbo.produce prod

You will struggle with this error in the SQL server if you have multiple joins. Also, if you give an alias name that the SQL server does not accept, you will end up with this error. One alias that SQL server does not accept is “oa”

– Example 3: Triggering the Error Using Join Statements

Suppose you want to use join statements to select details such as sales order, sales detail, and product from a table. You can try to accomplish this as follows:

SELECT * FROM sales.SalesOrder SO

LEFT OUTER JOIN Sales. SalesDetails SD

LEFT OUTER JOIN Produce.Product P

ON SD.ProduceID=p.ProduceID and SO.SalesOrderID=SD.SalesOrderID

If you run this SQL query you trigger the error in question. The reason you will get this error is SO table is not visible from the third part of your join statement and if you try to use the join statement you will get the error.

How To Solve the Error

In this section, you will learn how to fix the multi-part identifier could not be bound error. The solutions, of course, vary based on what is causing the error. Here is a quick look at some of the common solutions to this error.

– Solution 1: Use Assigned Aliases

When you assign aliases to tables, you should take note of those to reference them in later queries. If you already have aliases, make sure you use them instead of directly using the table name.

Example

Suppose in your previous example of a database with cat and dog tables, you want to show all the names of the cats and dogs in the tables. You can accomplish this using aliases as follows:

SELECT * FROM Cat c

INNER JOIN Dog d

ON c.CatName = d.DogName;

Also, you can opt to forego the use of aliases as follows:

SELECT * FROM Cat

INNER JOIN Dog

ON Cat.CatName = Dog.DogName;

– Solution 2: The Correct Use of Schema

You should avoid writing the wrong schema in your query. If you have a wrong schema in your query that selects produce from a table, you can correct it as follows:

SELECT prod.name

FROM dbo.produce prod

– Solution 3: Using Joins Correctly

You can have a strategy for doing inner joins and outer joins in your tables. First, you can do the inner join between your table followed by two left outer joins of your tables. This way you can mix outer joins and inner joins to get outcomes with data from different tables. Using this approach, you avoid using the default comma-separated syntax, which can miss rows in the desired join.

The best approach is to start with the inner join between tables that have matching data. After you are done, you can move to outer joins to attempt to collect data from other tables. The outer joins will not filter out the outcome if the outer join table does not have corresponding data.

Also, this applies if the outer join table does meet the condition you set in the condition or predicate.

Here is an example of how you can use a multi-part identifier in your query.

SELECT * FROM sales.SalesOrder SO

LEFT OUTER JOIN Sales. SalesDetails SD

ON SO.SalesOrderID=SD.SalesOrderID

LEFT OUTER JOIN Produce.Product P

ON SD.ProduceID=p.ProduceID

In this query, the SO.SalesOrderID=SD.SalesOrderID statement comes immediately after your first join, where the SO table is completely visible.

Conclusion

In this post, you have learned about the multi-part identifier could not be bound error. Here is a quick look at what you have covered in this article:

The error typically occurs when you are executing a SQL query, and the query is unable to resolve a multi-part identifier
This error arises from a variety of issues, such as spelling errors, incorrect table or column names, aliases, references, or permissions
To solve the error, you need to carefully examine the query and identify if you are correctly using table or column names, aliases, and references

With this understanding, you will be able to handle this error when it pops up in the future.

Author
Recent Posts

Your Go-To Resource for Learn & Build: CSS,JavaScript,HTML,PHP,C++ and MYSQL. Meet The Team

Источник

Remove From My Forums

Question

PRINT 'ReceiptDeliveryWay filling has started'

DECLARE @ReceiptDeliveryWay_Tmp TABLE(
	[ID]   TINYINT,
	[Name] VARCHAR (50)
)

INSERT INTO @ReceiptDeliveryWay_Tmp
SELECT 1, 'UniqueId' 
UNION SELECT 2, 'BarcodeImage' 
UNION SELECT 3, 'TicketImageToNFCModule'

--Слияние временной таблица с целевой
SET NOCOUNT OFF

DELETE FROM ReceiptDeliveryWay WHERE [ID] NOT IN (SELECT [ID] FROM @ReceiptDeliveryWay_Tmp)

UPDATE ReceiptDeliveryWay 
SET
t.[ID] = s.ID,
t.[Name] = s.Name
FROM ReceiptDeliveryWay t, @ReceiptDeliveryWay_Tmp s
WHERE t.ID = s.ID

PRINT 'ReceiptDeliveryWay filling has finished'
GO

When I try to run this script I receive «The multi-part identifier «t.ID» could not be bound.»

Answers

First try changing your update statement to this code so that you are using proper joins. Your problem is that you were saying to update ReceptDeliveryWay and then using the alias that you declared in your from satatement in the SET
```
UPDATE t
SET t.Name = s.NAME
FROM ReceiptDeliveryWay t
INNER JOIN @ReceiptDeliveryWay_Tmp s
	ON t.ID = s.ID


-------------------
```
Chuck Pedretti | Magenic – North Region | magenic.com
- Proposed as answer by
  
  Thursday, May 31, 2012 1:59 PM
- Edited by
  Chuck Pedretti
  Thursday, May 31, 2012 2:34 PM
  removed update to ID column — not needed
- Marked as answer by
  EngineerSpock
  Monday, June 18, 2012 12:15 PM

Источник

Reproducing the “Multi Part Identifier Could not be Bound” Error Message

Learn more tips like this! Enroll to our Online Course!

Explaining and Resolving the Error

Analysis and Discussion

Watch video: The Multi Part Identifier Could not be Bound – How to Resolve in SQL Server

Featured Online Courses:

Check some other related error messages and ways to resolve them:

Example

The Solution

Example

The Solution

What Triggers the Multi Part Identifier Could Not Be Bound Error?

– Popular Causes of This Error

– Variations of This Error

– Example 1: How To Trigger This Error Using Aliases

– Example 2: Wrong Use of Dbo Schema

– Example 3: Triggering the Error Using Join Statements

How To Solve the Error

– Solution 1: Use Assigned Aliases

Example

– Solution 2: The Correct Use of Schema

– Solution 3: Using Joins Correctly

Conclusion

Question

Answers