Mysql error message "Unknown column in on clause"

[Carnie]

Hi
I have recently switched some applications from mysql 4 to mysql 5. Then I found that queries with left outer joins do not work any more. Assume three tables t1, t2, t3 with their corresponding attributes t1.a, t2.b, t3.c. The exact same query worked in Mysql 4, I think this is a valid sql query and does not work in Mysql 5. I am trying the following query please check if this work on your computer.

mysql> select * from t1, t2 left outer join t3 on t1.a=t3.c;
ERROR 1054 (42S22): Unknown column 't1.a' in 'on clause'

Any help will be appreciated. Thanks in advance.

[Modifier]

Hi
I think this is the know bug in Mysql 5. If you want to work around this, is to specify t1 join t2 instead of using the ","(comma). the following is the example of this. It worked for me and hopefully will work for you.

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

[opaper]

Hello
I had the same problem, but fortunately it is not a problem for me now and to fix it is a very simple way. All you have to do is just add parentheses to the join after the FROM, as show in the example below

select * from (t1, t2) join t3 on t1.a=t3.c;
select * from (t1, t2) left outer join t3 on t1.a=t3.c;

This solution worked on me, and definitely should work for you. This is because MySql has now added some new rules for Sql language. There are more solutions for this, but I think the above is much more easier than others.

[Carnie]

Hi,
Thanks guys.
I was looking around for this solution, but could not find the way around. Thanks for you guys, both the above solutions work perfectly for me. But just guessing why Sql is changing the rules. Forget it, the solution is working fine and thanks again.