|
|
Error Message
Server: Msg 1013, Level 16, State 1, Line 1
The objects "Object Name" and "Object Name" in the FROM
clause have the same exposed names. Use correlation
names to distinguish them.
Causes
This error happens when you reference a table at least twice in the FROM clause and you did not specify a table alias to either table so that SQL Server can distinguish one from the other.
|
To better illustrate, let’s assume you have a table called [dbo].[Employees] which contains all employees in the company. Both employees and managers are defined in this same table.
CREATE TABLE [dbo].[Employees] (
[EmployeeID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[FirstName] VARCHAR(50) NOT NULL,
[LastName] VARCHAR(50) NOT NULL,
[ManagerID] INT NULL
REFERENCES [dbo].[Employees] ( [EmployeeID] )
)
GO
For the benefit of showing how the error can happen, let’s assume that as a beginner in SQL Server you came up with the following SELECT statement in generating a list of employees with their corresponding managers.
SELECT *
FROM [dbo].[Employees] INNER JOIN [dbo].[Employees]
ON [ManagerID] = [EmployeeID]
GO
Executing this query will generate the error:
Msg 1013, Level 16, State 1, Line 1
The objects "dbo.Employees" and "dbo.Employees" in the FROM clause have the same exposed names.
Use correlation names to distinguish them
Solution / Workaround:
To address this error message, all you have to do is to assign a unique table alias to each table referenced in the FROM clause and use that table alias in referencing the columns.
In the example above, we can assign Emp as the table alias (or correlation name) for the first reference to the [dbo].[Employees] table which will refer to the employee and we can assign Mgr as the table alias for the second reference to the [dbo].[Employees] table, which will refer to the manager.
SELECT Emp.[EmployeeID],
Emp.[FirstName] AS [EmployeeFirstName],
Emp.[LastName] AS [EmployeeLastName],
Mgr.[FirstName] AS [ManagerFirstName],
Mgr.[LastName] AS [ManagerLastName]
FROM [dbo].[Employees] Emp INNER JOIN [dbo].[Employees] Mgr
ON Emp.[ManagerID] = Mgr.[EmployeeID]
GO
Make sure to use a unique table alias or correlation name for each table because if by accident you use the same table alias, you will get the following error message:
SELECT Emp.[EmployeeID],
Emp.[FirstName] AS [EmployeeFirstName],
Emp.[LastName] AS [EmployeeLastName],
Emp.[FirstName] AS [ManagerFirstName],
Emp.[LastName] AS [ManagerLastName]
FROM [dbo].[Employees] Emp INNER JOIN [dbo].[Employees] Emp
ON Emp.[ManagerID] = Emp.[EmployeeID]
GO
Msg 1011, Level 16, State 1, Line 2
The correlation name 'Emp' is specified multiple times in a FROM clause.
|