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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
