From: Peter Brawley
Date: November 24 2005 2:29am
Subject: Re: LEFT JOIN not working on 5.0.16 - urgent help
List-Archive: http://lists.mysql.com/mysql/192116
Message-Id: <43852572.40902@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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