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;



Article ID: 19
Created On: Mon, Mar 2, 2015 at 5:05 PM
Last Updated On: Thu, Apr 23, 2015 at 10:39 AM

Online URL: https://www.heartinternet.uk/support/article/why-is-mysql-5-saying-it-can-t-find-a-column-that-exists.html