Column in where clause is ambiguous

A recent task to investigate and fix a bug within a PHP / MySQL website highlighted a simple but important rule when writing SQL which I’d like to share.

The website used PHP and MySQL in a very normal way, using MySQL to run queries and PHP to display the results.  The bug was reported not long after some updates had been completed on another part of the website.  After reviewing the logs it was obvious what the error was but the cause was not as simple to find.

The error message is very clear.  This is caused when a query uses 2 or more tables with the same column names which have not been prefixed.

A simple example

Table: competition

id competition_name date
13 Competition 1 2013-04-21
15 Competition 2 2013-04-22
16 Competition 3 2013-04-23

Table: event

competition _id event_name
13 Event 1
15 Event 2
16 Event 3

A query to show the competitions with their events is very simple

Select id, competition_name, date, event_name 
from competition c 
left join event e 
on e.competition_id = c.id

This gives us

id competition_name date  event_name
13 Competition 1 2013-04-21  Event 1
15 Competition 2 2013-04-22  Event 2
16 Competition 3 2013-04-23  Event 3

So the query works and everything is cool. Now imagine a few months or even years later an id field is added to the event table, seems OK right?
So the event table now looks like;

id competition _id event_name
1 13 Event 1
2 15 Event 2
3 16 Event 3

And BAAM, the original query which worked for months or years has now stopped working and has started filling up the error logs with;

Column id in where clause is ambiguous

This is because both tables now return id and MySQL doesn’t make the choice for you.

The solution

The solution isn’t to rename your columns, there’s nothing wrong with having 2 id columns.  The solution is to prefix the column names with the table like so;

Select c.id, c.competition_name, c.date, e.event_name 
from competition c 
left join event e on e.competition_id = c.id

If the query was written like this from the beginning then this issue would never have happened.  A very simple but effective method of future proofing your SQL.

No Comments