List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:January 28 2008 4:25pm
Subject:Re: left join in version 5.0.45
View as plain text  
Malki,

 >which gives the error:
 >ERROR 1054 (42S22): Unknown column 'depts.dept_code' in 'on clause'

See the JOINs page of the manual. As of 5.0.12, MySQL improved ISO SQL 
compliance in query parsing; ambiguities due to comma join syntax became 
errors. It's highly recommended to switch from comma joins to explicit 
join syntax.

PB

Malki.Cymbalista@stripped wrote:
> We have just moved to a new machine where we are running MySQL 5.0.45. 
> On the old machine we were running MySQL 4.0.15a.
> The following query worked in 4.0.15a but gives an error mesage in 
> 5.0.45. We're pretty sure the problem is with the left join.  Did 
> anything change?
>
> Here's the query:
> select distinct  depts.dept_code, depts.sci_id, 
> hr.title,hr.fname,hr.lname, aptb.entry_date  from depts, hr left join 
> crdepts aptb on  depts.dept_code=aptb.dept_code  where 
> depts.sci_id=hr.sci_id && depts.dept_code=90;
>
> which gives the error:
> ERROR 1054 (42S22): Unknown column 'depts.dept_code' in 'on clause'
>
> When I take out the left join and give the query
> select distinct  depts.dept_code, depts.sci_id, 
> hr.title,hr.fname,hr.lname, aptb.entry_date  from depts, hr, crdepts 
> aptb where depts.dept_code=aptb.dept_code  and depts.sci_id=hr.sci_id 
> && depts.dept_code=90;
>
> I get a proper result.
>
> Did anything change with left join?
> Thanks for any help.
>
> Malki Cymbalista
> Webmaster, Weizmann Institute of Science
> Rehovot, Israel 76100
> Internet: Malki.Cymbalista@stripped
>
Thread
left join in version 5.0.45Malki.Cymbalista28 Jan
  • Re: left join in version 5.0.45Baron Schwartz28 Jan
  • Embedded versus 'normal' MySQLphilip28 Jan
  • Re: left join in version 5.0.45Peter Brawley28 Jan