List:General Discussion« Previous MessageNext Message »
From:Terence Date:November 24 2005 1:22am
Subject:LEFT JOIN not working on 5.0.16 - urgent help
View as plain text  
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: terence@stripped
Phone: (+603) 7491 8623  ext. 8078
Website: http://www.sunway.edu.my
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