|
|
SQL Server Error Messages - Msg 209
Error Message
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name '<Column Name>'.
Causes
This error happens when you are joining 2 or more tables in a statement and there are columns among these tables that have the same name and you didn?t prefix the column name with the table name when referencing the column in your statement.
|
To illustrate, let's say you have the following tables which contains the [dbo].[Employees] and [dbo].[Supervisors] of your company:
CREATE TABLE [dbo].[Supervisors] (
[SupervisorID] INT,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50)
)
CREATE TABLE [dbo].[Employees] (
[EmployeeID] INT,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[SupervisorID] INT
)
You want to generate a list of your employees together with the name of their corresponding supervisors. In doing so, you issued the following SELECT statement:
SELECT [FirstName] AS [EmployeeFirstName],
[LastName] AS [EmployeeLastName],
[FirstName] AS [SupervisorFirstName],
[LastName] AS [SupervisorLastName]
FROM [dbo].[Employees] Emp INNER JOIN [dbo].[Supervisors] Sup
ON Emp.[SupervisorID] = Sup.[SupervisorID]
Issuing this SELECT statement in SQL Server Management Studio will generate the following errors:
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'FirstName'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'LastName'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'FirstName'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'LastName'.
Solution / Work Around
To avoid this error, make sure that you prefix the column name with the table name or table alias for those columns that exist in more than 1 table. Here's an updated SELECT statement that will not generate the error:
SELECT Emp.[FirstName] AS [EmployeeFirstName],
Emp.[LastName] AS [EmployeeLastName],
Sup.[FirstName] AS [SupervisorFirstName],
Sup.[LastName] AS [SupervisorLastName]
FROM [dbo].[Employees] Emp INNER JOIN [dbo].[Supervisors] Sup
ON Emp.[SupervisorID] = Sup.[SupervisorID]
It is a good practice to always prefix column names with the table name or table alias so that in case additional columns are added to a table that have the same name as existing columns in another table, you will not encounter this error.
|