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:

Notice: Error: Column 'firstname' in where clause is ambiguous
Error No: 1052
SELECT COUNT(*) AS total FROM oc_customer c LEFT JOIN oc_address a ON (c.customer_id = a.customer_id) WHERE CONCAT(firstname, ' ', lastname) LIKE '%john%'

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.

SELECT COUNT(*) AS total FROM oc_customer c LEFT JOIN oc_address a ON (c.customer_id = a.customer_id) WHERE CONCAT(c.firstname, ' ', c.lastname) LIKE '%john%'

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.

Did you find this article helpful?

We value your feedback!

Why was this article not helpful? (Check all that apply)
The article is too difficult or too technical to follow.
There is a step or detail missing from the instructions.
The information is incorrect or out-of-date.
It does not resolve the question/problem I have.
How did you find this article?
Please tell us how we can improve our Support Center:
Email Address
Optional, but our team may contact you for more information.
Like this Article?

Post a Comment

Name:
Email Address:
Comment:
Are you a bot?
Submit

Please note: Your name and comment will be displayed, but we will not show your email address.

Related Questions

Here are a few questions related to this article that our customers have asked:
Ooops! It looks like there are no questions about this page.
Would you like to ask a question about this page? If so, click the button below!
Ask a Question

Need more Help?

Search

Ask the Community!

Get help with your questions from our community of like-minded hosting users and InMotion Hosting Staff.

Current Customers

Chat: Click to Chat Now E-mail: support@InMotionHosting.com
Call: 888-321-HOST (4678) Ticket: Submit a Support Ticket

Not a Customer?

Get web hosting from a company that is here to help. Sign up today!