Multipart identifier could not be bound ошибка - Решение и исправление самых разных ошибок на TopOshibok.ru

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.

Источник

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;

Источник

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.

Источник

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

Источник

The following is a HAVING clause from a small part of a very large stored procedure which I have inherited from some other devs (external);

HAVING (SELECT COUNT(*) FROM
(
    SELECT *
    FROM dbo.ContractDailyRoomAllocation da2
    WHERE da2.ContractId = DA.ContractId
    AND da2.RoomTypeId = DA.RoomTypeId
    AND da2.Date >= @FromDate AND da2.Date < @ToDate
    AND da2.IsSold = 0
    AND da2.ReleaseDay <= DATEDIFF("d", @TodayDate, da2.Date)) T) = @StayDates
    AND ( (@AllowOnRequestBookings = 'False' 
      OR dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate)  
      >= 0 ) )
      AND ( (@AllowOnRequestBookings = 'True' 
      OR dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) 
      > 0 )
)

The last 2 AND‘s give me the following error messages:

Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 176
The multi-part identifier «da2.ContractId» could not be bound.
Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 176
The multi-part identifier «da2.RoomTypeId» could not be bound.
Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 177
The multi-part identifier «da2.ContractId» could not be bound.
Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 177
The multi-part identifier «da2.RoomTypeId» could not be bound.

Any ideas?

Источник

Example

The Solution

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:

Question

Answers