InterBase (29)
MS-SQL (5)
mysql (37)
Oracle (1)
Exchange Links
About this site
mysql message ERROR 1054 (42S22): Unknown column in 'on clause'
4 comments. Current rating: 
(2 votes). Leave comments and/ or rate it.
Question:
I migrated a mysql application from mysql 4 to mysql 5. I found that some queries with left outer joins do not work any more.Imagine 3 tables t1,t2,t3 with attributes t1.a, t2.b and t3.c
The following query worked in mysql 4, is valid SQL and does not work in mysql 5:
mysql> select * from t1, t2 left outer join t3 on t1.a=t3.c;
ERROR 1054 (42S22): Unknown column 't1.a' in 'on clause'
Answer:
This is a known bug in mysql 5. The work around is to specify t1 join t2 instead of using the , (comma). |  | |
create table t1 (a int); create table t2 (b int); create table t3 (c int); select * from t1, t2 join t3 on t1.a=t3.c; ERROR 1054 (42S22): Unknown column 't1.a' in 'on clause' select * from t1, t2 left outer join t3 on t1.a=t3.c; ERROR 1054 (42S22): Unknown column 't1.a' in 'on clause' select * from t1 join t2 join t3 on t1.a=t3.c; //.. this works .. select * from t1 join t2 left outer join t3 on t1.a=t3.c; //.. this works .. | ||
 |  |
Comments:
