Error 1052 Column in where clause is ambiguous

When working with programs that pull data from databases, you may occasionally run across different types of errors. Many of them are fixable, specifically if you are coding your own SQL queries. This article describes the ‘1052 Column in where clause is ambiguous‘ error and how to correct it.

Correcting the ‘1052 Column in where clause is ambiguous’ error

The Error

This type of error occurs when a SQL query is working with more than one table. Our example below is using two tables within an ecommerce program and generates the following error:

What causes the error

The query gives each table an alias, the oc_customer table gets an alias of c and the oc_address table gets an alias of a. This is used to define which table the column is supposed to come from, such as the section of the query c.customer_id = a.customer_id. This is the same as saying oc_customer.customer_id = oc_address.customer_id. There is another section that has column names but the script fails to identify which table to look into, CONCAT(firstname, ‘ ‘, lastname). Since both the oc_customer and oc_address tables have columns named firstname and lastname, the server does not know which table to work with, and thus throws the error.

Fixing the error

To fix this, simply add the tablename or alias for the table you want to work with. If you are writing the query yourself, this is a bit easier to deal with as you will know which table you meant to use. In our example, we should add the alias for the oc_customer table, c, to the column names. Our code snippet would look like this: CONCAT(c.firstname, ‘ ‘, c.lastname) making the whole query appear as below.

If you are working with a developer or a professional level program (open source or purchased) you will need to let them know of the error. They will be able to fix the issue in their next release.

The query will now run correctly as it knows which table to work with on all columns.