|
|
Error Message
Server: Msg 4104, Level 16, State 1, Line 1
The multi-part identifier could not be bound.
Causes
This error usually occurs when an alias is used when referencing a column in a SELECT statement and the alias used is not defined anywhere in the FROM clause of the SELECT statement.
|
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]
|