Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 21-11-2009
Member
 
Join Date: Oct 2009
Posts: 82
Mysql error message "Unknown column in on clause"

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.
Code:
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.
Reply With Quote
  #2  
Old 21-11-2009
Member
 
Join Date: Jan 2008
Posts: 1,515
Re: Mysql error message "Unknown column in on clause"

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.
Code:
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 ..
Reply With Quote
  #3  
Old 21-11-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: Mysql error message "Unknown column in on clause"

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
Code:
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.
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
  #4  
Old 21-11-2009
Member
 
Join Date: Oct 2009
Posts: 82
Re: Mysql error message "Unknown column in on clause"

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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Mysql error message "Unknown column in on clause""
Thread Thread Starter Forum Replies Last Post
Get message "Unknown USB Device" on Toshiba Satellite A215-S7422 Eta!! Portable Devices 10 04-11-2011 10:51 AM
Application Error: The instruction at "0x7c91b21a" referenced memory at "0x00000010". Message on bootup Jaiya Operating Systems 5 19-04-2011 08:54 AM
Getting "Open error 184467" and "unknown error: -22" errors on my Macbook pro The creeper Portable Devices 3 09-12-2010 07:09 AM
Adobe Reader v9.3 getting "unknown file type" message on download Elila Windows Software 4 26-06-2010 04:36 PM
MySql gives "too many transactions" error message Kasper Software Development 4 29-01-2010 06:14 PM


All times are GMT +5.5. The time now is 04:42 PM.