From: Harald Kleiner <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: unexpected results using LEFT JOIN with more than one join_condition
>Description: I use LEFT JOIN to join together two tables. And (for some 'complex' reason) I want to add additional conditions into the join_condition-part (and not in the WHERE part where it would belong to). But these additional conditions seem to be ignored just as if they weren't there. If I cut the 'ON'-part and move conditions to the 'WHERE' all goes the way it should. Changing the join method from LEFT JOIN to INNER JOIN does fix the problem - yes but alas I need a LEFT JOIN in this case. The manual says "The ON conditional is any conditional of the form that may be used in a WHERE clause." So it _should_ be allowed to do the things that I do... Maybe this is all my fault - so please be patient. But in any case: Thank you very much for spending time with my problem! >How-To-Repeat: I have constructed a simplified example that shows the unexpected behavior. A table called person is linked with room-table. Both have a date-typed field that holds deletion-date of the record. Now I want to get all persons with their room codes which are _not_ deleted (i.e. deleted==0). As you can see the first query uses LEFT JOIN ... ON ... and returns all four persons. (Joe gets returned even though he has a non-zero deletion-date -- that's what I don't understand) And in the second query I just replaced the first 'AND' with a 'WHERE' and voila Joe falls out (because of his non-zero deletion-date) and Fred falls out because of his null room (room.deleted gets null I think) -- that's what I expected to get. DROP DATABASE JOINTest; CREATE DATABASE JOINTest; USE JOINTest; CREATE TABLE person ( personID INT AUTO_INCREMENT NOT NULL, roomID INT, name VARCHAR(20), deleted DATE, PRIMARY KEY(personID) ); CREATE TABLE room ( roomID INT AUTO_INCREMENT NOT NULL, roomcode VARCHAR(20), deleted DATE, PRIMARY KEY(roomID) ); INSERT INTO person VALUES(NULL, 0, 'Fred', 0); INSERT INTO person VALUES(NULL, 1, 'Max', 0); INSERT INTO person VALUES(NULL, 1, 'Joe', '2002-07-01'); INSERT INTO person VALUES(NULL, 1, 'Anne', 0); INSERT INTO room VALUES(NULL, 'R001', 0); SELECT DISTINCTROW person.name, person.deleted, room.roomcode FROM person LEFT JOIN room ON person.roomID=room.roomID AND person.deleted=0 AND room.deleted=0; +------+------------+----------+ | name | deleted | roomcode | +------+------------+----------+ | Fred | 0000-00-00 | NULL | | Max | 0000-00-00 | R001 | | Joe | 2002-07-01 | NULL | | Anne | 0000-00-00 | R001 | +------+------------+----------+ 4 rows in set (0.00 sec) SELECT DISTINCTROW person.name, person.deleted, room.roomcode FROM person LEFT JOIN room ON person.roomID=room.roomID WHERE person.deleted=0 AND room.deleted=0; +------+------------+----------+ | name | deleted | roomcode | +------+------------+----------+ | Max | 0000-00-00 | R001 | | Anne | 0000-00-00 | R001 | +------+------------+----------+ 2 rows in set (0.00 sec) >Fix: Yes, I could move additional criteria into the WHERE but that would be not as beautiful and easy for me as putting it into the ON - part. And even in the manual (chapter 9.8.1, The Twin Project) such a join is used. So it _should_ work. >Submitter-Id: >Originator: Harald Kleiner >Organization: >MySQL support: none >Synopsis: unexpected LEFT JOIN behavior >Severity: non-critical >Priority: low >Category: mysql >Class: sw-bug | support >Release: mysql-3.23.33 (Source distribution) >Server: /usr/bin/mysqladmin Ver 8.15 Distrib 3.23.33, for suse-linux on i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.33-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 54 min 52 sec Threads: 3 Questions: 618 Slow queries: 0 Opens: 56 Flush tables: 1 Open tables: 50 Queries per second avg: 0.188 >Environment: SuSE Linux 7.1 (but with some updates already) on an AMD Duron, 256MB RAM System: Linux haraldk 2.4.7-4GB #1 Thu Oct 25 19:17:11 GMT 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='-O2 -m486' CXX='c++' CXXFLAGS='-O2 -m486' LDFLAGS='' LIBC: -rwxr-xr-x 1 root root 1382179 Jan 19 2001 /lib/libc.so.6 -rw-r--r-- 1 root root 2585872 Jan 19 2001 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Jan 19 2001 /usr/lib/libc.so -rw-r--r-- 1 root root 851660 Jan 19 2001 /usr/lib/libc-client.a lrwxrwxrwx 1 root root 19 Apr 24 23:52 /usr/lib/libc-client.so -> libc-client.so.2000 -rwxr-xr-x 1 root root 714876 Jan 19 2001 /usr/lib/libc-client.so.2000 Configure command: ./configure --enable-shared --enable-assembler --infodir=/usr/share/info --libdir=/usr/lib --libexecdir=/usr/sbin --local --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php