List:General Discussion« Previous MessageNext Message »
From:SGreen Date:November 24 2005 5:26am
Subject:Re: LEFT JOIN not working on 5.0.16 - urgent help
View as plain text  
Terence <terence@stripped> wrote on 11/23/2005 08:22:30 PM:

> 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
> 

The SQL didn't change, the query engine was debugged. That is the error 
you should have been getting all along but weren't. Please check the 
manual for a full explanation but the short version is that crappy 
comma-delimited method of making a CROSS JOIN has been demoted in 
evaluation priority (where it should be). Three options:

a) swap the order you list the tables so that user_master appears next to 
the LEFT JOIN
b) use parentheses to reprioritize the joins so that user_master CROSS 
JOINs to role_master BEFORE you LEFT JOIN to department_master
c) quit using the comma. Use CROSS JOIN instead.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
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