Why is MySQL 5 saying it can’t find a column that exists?

If you get the error:

1054 – Unknown column ‘a.c’ in ‘on clause’

This could be because of an invalid SQL request.

If you are producing SQL that looks like:

SELECT * FROM
a, b
LEFT JOIN c ON a.d = c.e;

That can produce the error.  It may look fine, and it did work in previous versions of MySQL, but the SQL standard means that MySQL 5 reads it as:

SELECT * FROM
a,
b LEFT JOIN c ON a.d = c.e;

Where the LEFT JOIN no longer makes any sense.

To fix this problem, either move the JOIN statement so that it is between the two tables it is joining:

SELECT * FROM
a LEFT JOIN c ON a.d = c.e,
b;

Or explicitly group like ((a,b),c) instead of (a,(b,c)):

SELECT * FROM
(a,
b) LEFT JOIN c ON a.d = c.e;