SQL Server Portal

In SQL Server, alias are very helpful, because you do not need to write the long table names again and again. I usually use ALIAS in my development to speed up the work. Yesterday, I was working on a complex query and I used a lot of alias to minimize the line of code and finally I came across an error.

Let’s discuss this in detail:
Message Number: 1011

Severity : 16

Error Message: The correlation name ‘%.*ls’ is specified multiple times in a FROM clause.

Error Generation:

Given below is the script that I tried to execute but it resulted with the following error.

Use AdventureWorks2012
GO

SELECT
p.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,p.[JobTitle]
FROM [HumanResources].[Employee] p
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]

GO

Msg 1011, Level 16, State 1, Line 2
The correlation name ‘p’ is specified multiple times in FROM clause.

Ooopps…… I am unable to execute the above qquery.

Resolution:
The resolution is very simple. Basically, you aliased more than one table with the same name, so SQL Server is unable to relate alias with table name. In the above table, I ALIASED both tables with p while I can assign one name (p) as an alias to one table only and I must change the alias of other table name (p) to something else. (That make sense also). Lets rewrite the script with the different alias and it works…….

USE [AdventureWorks2012]
GO
SELECT
e.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,e.[JobTitle]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
GO
--OUTPUT