Re: LEFT JOIN not working on 5.0.16 - urgent help

2005-11-23 Thread SGreen 
Terence <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
> 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

Re: LEFT JOIN not working on 5.0.16 - urgent help

2005-11-23 Thread Peter Brawley 

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]

Re: LEFT JOIN not working on 5.0.16 - urgent help

2005-11-23 Thread Simon Garner 

On 24/11/2005 2:22 p.m., Terence wrote:


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?



Yeah this caught me out too. The precedence of JOINs has changed to more 
strictly follow the SQL standard.


In 5.0, MySQL is now interpreting your query as:

SELECT ... FROM user_master um, (role_master rm LEFT JOIN 
department_master dm ON um.department_id = dm.department_id) ...


And the table um doesn't exist in the join between rm and dm. The 
solution is to put the FROM tables in parentheses, like:


SELECT ... FROM (user_master um, role_master rm) LEFT JOIN 
department_master dm ON um.department_id = dm.department_id) ...


You can read more about this here:
http://dev.mysql.com/doc/refman/5.0/en/join.html

-Simon

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

LEFT JOIN not working on 5.0.16 - urgent help

2005-11-23 Thread Terence 

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]