I'm getting SQL errors on MySQL 5 saying it can't find a column - but it's there!

Knowledge Base

I'm getting SQL errors on MySQL 5 saying it can't find a column - but it's there!

1054 - Unknown column 'a.c' in 'on clause' 

This can happen if you're doing something like: 


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

 

...which looks, on the surface of it, fine. But it's invalid SQL; bugs in earlier versions of MySQL (4.x) caused it to be parsed as above and appear to work, but the SQL standard says it should be read as: 


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

 

 

...and you can see that the LEFT JOIN no longer makes any sense. So, to fix your SQL, you need to either move the JOIN statement so it's between the two tables it's 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;