Tip of the Day : SQL Server 2012 Management Studio Keyboard Shortcuts - Document Window and Browser
Home > SQL Server Error Messages > Msg 107 - The column prefix does not match with a table name or alias name used in the query.
SQL Server Error Messages - Msg 107 - The column prefix does not match with a table name or alias name used in the query.

SQL Server Error Messages - Msg 107

Error Message

Server: Msg 107, Level 16, State 3, Line 1
The column prefix does not match with a table name or alias name used in the query.

Causes

The most common cause of this error is when using a table name as a prefix of a column in a query and the table name is not part of the FROM clause. Another cause of this error is when an alias is assigned to a table in the FROM clause and the prefix used for a column is still the table name and not the alias.

To illustrate the first cause of this error as mentioned above, the following SELECT statement will generate the error because one of the column prefixes used does not match the table specified in the FROM clause:

SELECT [Employees].[FirstName], [Customers].[LastName]
FROM [dbo].[Customers]
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'Employees' does not match with a table name or alias name used in the query.

To illustrate the second cause of this error, the following SELECT statement will generate the error:

SELECT [Customers].[FirstName], [Customers].[LastName]
FROM [dbo].[Customers] Cust
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'Customers' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Customers' does not match with a table name or alias name used in the query.

Solution / Work Around :

To avoid this error, always make sure that the table name used as a prefix of a column in a query exists in the FROM clause.

SELECT [Customers].[FirstName], [Customers].[LastName]
FROM [dbo].[Customers]

Also, to avoid the second cause of this error, once you assign an alias to a table in the FROM clause, make sure to use that alias in a column prefix and not the original table name:

SELECT [Cust].[FirstName], [Cust].[LastName]
FROM [dbo].[Customers] Cust