The multi-part identifier ... could not be bound

There are cases where a database developer might get the error message "The multi-part identifier [database_entity_name] could not be bound".

This happens because of the way table scopes are handled by the developer within the query.

Let's see a relevant example:

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


he/she would end up with the following error message:

The multi-part identifier "addr.country" could not be bound.

The problem in the above T-SQL Statement is that even though we are using the addr table alias in the subquery, we are not syntactically allowed to use it outside the scope of the subquery, that is in this example, 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

Remarks
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.


If you are interested in SQL Server's In-Memory OLTP, check out my latest software tool "In-Memory OLTP Simulator"!

Labels: , ,