Hi, On Jan 28, 2008 5:31 AM, <[EMAIL PROTECTED]> 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?
Yes. Don't mix comma-join and ANSI join syntaxes. The precedences are terribly confusing now. To see how MySQL interprets your query, you can use EXPLAIN EXTENDED followed by SHOW WARNINGS. > > 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: [EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
