List:General Discussion« Previous MessageNext Message »
From:Simon Garner Date:November 24 2005 1:48am
Subject:Re: LEFT JOIN not working on 5.0.16 - urgent help
View as plain text  
On 24/11/2005 2:22 p.m., Terence wrote:
> 
> SELECT um.username,rm.role_name,dm.department_name
> FROM user_master um, role_master rm
> LEFT JOIN department_master dm ON um.department_id = dm.department_id
> WHERE um.role_id = rm.role_id;
> 
> 
> 1054 - Unknown column 'um.department_id' in 'on clause'
>  Query:
>  SELECT um.username,rm.role_name,dm.department_name
> FROM user_master um, role_master rm
> LEFT JOIN department_master dm ON um.department_id = dm.department_id
> WHERE um.role_id = rm.role_id
> =====
> 
> 
> Has the left join syntax changed?
> 

Yeah this caught me out too. The precedence of JOINs has changed to more 
strictly follow the SQL standard.

In 5.0, MySQL is now interpreting your query as:

SELECT ... FROM user_master um, (role_master rm LEFT JOIN 
department_master dm ON um.department_id = dm.department_id) ...

And the table um doesn't exist in the join between rm and dm. The 
solution is to put the FROM tables in parentheses, like:

SELECT ... FROM (user_master um, role_master rm) LEFT JOIN 
department_master dm ON um.department_id = dm.department_id) ...

You can read more about this here:
http://dev.mysql.com/doc/refman/5.0/en/join.html

-Simon
Thread
LEFT JOIN not working on 5.0.16 - urgent helpTerence24 Nov
  • Re: LEFT JOIN not working on 5.0.16 - urgent helpSimon Garner24 Nov
  • Re: LEFT JOIN not working on 5.0.16 - urgent helpPeter Brawley24 Nov
  • Re: LEFT JOIN not working on 5.0.16 - urgent helpSGreen24 Nov
    • select commandasus77x24 Nov
      • Re: select commandRhino24 Nov
      • Re: select commandGleb Paharenko24 Nov
Re: select commandRhino25 Nov
  • RE: select commandasus77x25 Nov