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
