DelphiFAQ Home Search:
General :: Databases :: mysql
General information about mysql - how to get around the differences between version 3.33 an 4, how to do stuff that you think you need a nested query for etc.

Articles:

This list is sorted by recent document popularity (not total page views).
New documents will first appear at the bottom.

Featured Article

mysql message ERROR 1054 (42S22): Unknown column in 'on clause'

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 ..
 

Generated 0:00:37 on Dec 13, 2017