There are cases where a database developer might get an error error message similar to: “The multi-part identifier 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.
CREATE TABLE [dbo].[Employee]( [id] [int] NOT NULL, [name] [varchar](50) NULL, [age] [int] NULL ) ON [PRIMARY]
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 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; GO
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.
For more SQL Server development articles check out the relevant post category on my blog.
Also, feel free to check out my eBooks on SQL Server.
Other SQL Server troubleshooting articles to check on SQLNetHub:
- Error converting data type varchar to float
- Operating System Error 170 (Requested Resource is in use)
- There is no SQL Server Failover Cluster Available to Join
- Installing SQL Server 2016 on Windows Server 2012 R2: Rule KB2919355 failed
- Setup failed to start on the remote machine. Check the Task scheduler event log on the remote machine.
- A connection was successfully established with the server, but then an error occurred during the login process.
- SQL Server 2008 R2 Service Pack Installation Fails – Element not found. (Exception from HRESULT: 0x80070490)
- 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, …
- … more SQL Server troubleshooting articles
Rate this article:
Make sure you check out my latest SQL Server eBook releases!
Reference: SQLNetHub (https://www.sqlnethub.com)
Recommended eBooks on SQL Server: