If you used to write MySQL joins for MySQL versions < 5.0 or upgrade your server from MySQL 4 to MySQL >= 5.0 you maybe run into a problem when you execute the following query:

SELECT *
FROM mytable1, mytable2
INNER JOIN mytable3
ON mytable1.mycolumnname = mytable3.mycolumnname
WHERE mytable1.id = mytable2.id;

#1054 – Unknown column ‘mytable.mycolumnname’ in ‘on clause’

Even though you made sure that the column exists, the problem persists. It can be a very annoying and time-consuming task to track this kind of error down to it’s cause: MySQL starting from version 5.0 tries to be more compliant to ANSI SQL. The tables are beeing joined in a different order. The solution to this problem is actually very simple. Surround the tables in the FROM clause with round brackets:

SELECT *
FROM (mytable1, mytable2)
INNER JOIN mytable3
ON mytable1.mycolumnname = mytable3.mycolumnname
WHERE mytable1.id = mytable2.id;

Even a better solution is to rewrite the query and make use of ANSI joins:

SELECT *
FROM mytable1
INNER JOIN mytable2
ON mytable1.id = mytable2.id
INNER JOIN mytable3
ON mytable1.mycolumnname = mytable3.mycolumnname;