From: Peter Brawley
Date: January 28 2008 4:25pm
Subject: Re: left join in version 5.0.45
List-Archive: http://lists.mysql.com/mysql/211108
Message-Id: <479E0207.4040201@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>