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?

--
Terence Le Grange
Senior IS Executive - ITS
Sunway University College
Email: [EMAIL PROTECTED]
Phone: (+603) 7491 8623  ext. 8078
Website: http://www.sunway.edu.my

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to