Terence,
>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;
>J
Your query joins rm to dm on a um column = a dm column. Recent 5.0 releases
rightly object to syntactical weirdness like that. You need something
like...
... FROM role_master rm
INNER JOIN user_master um USING (role_id)
LEFT JOIN department_master USING (department_id) ...
PB
-----
Terence wrote:
Hi All,
We have upgraded from 4.1.0 to 5.0.16 and notice all left joins not
working. It works fine on 4.1 but 5.0.16 gives us an error:
How to reproduce:
CREATE TABLE `user_master` (
`user_id` int(5) unsigned NOT NULL auto_increment,
`department_id` int(5) default NULL,
`role_id` int(5) unsigned default NULL,
`username` varchar(50) NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_id` (`user_id`),
KEY `user_id_2` (`user_id`)
);
CREATE TABLE `role_master` (
`role_id` int(5) unsigned NOT NULL auto_increment,
`role_name` varchar(50) NOT NULL,
PRIMARY KEY (`role_id`),
UNIQUE KEY `role_id` (`role_id`),
KEY `role_id_2` (`role_id`)
);
CREATE TABLE `department_master` (
`department_id` int(5) unsigned NOT NULL auto_increment,
`department_name` varchar(50) NOT NULL,
PRIMARY KEY (`department_id`),
UNIQUE KEY `department_id` (`department_id`),
KEY `department_id_2` (`department_id`)
);
INSERT INTO role_master(role_name) VALUES('Administrator');
INSERT INTO department_master(department_name) VALUES('ITS');
INSERT INTO user_master(department_id,role_id,username)
VALUES('1','1','Joey');
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?
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.6/179 - Release Date: 11/23/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
