Left Join more than one table

[vinita vigine Murugiah]

HI
I'm using ver 3:23:49 & 3:23:53, I thought
"A LEFT JOIN B USING (c)" is Identical to "A LEFT JOIN B ON A.c = B.c"

It seems it is NOT the case if you are joining more that ONE table & one of them is EMPTY. Please see the following Example

mysql> select * from room;
+---------+--------------+----------+
| roomNum | roomCapacity | roomType |
+---------+--------------+----------+
| L1.4 | 10 | tute |
| L1.6 | 26 | lab |
+---------+--------------+----------+
2 rows in set (0.00 sec)

mysql> select * from roomLock;
+---------+---------+
| roomNum | lockNum |
+---------+---------+
| L1.4 | K1 |
| L1.4 | K1.4 |
| L1.6 | k1.6 |
+---------+---------+
3 rows in set (0.00 sec)

mysql> select * from roomOwner;
Empty set (0.02 sec)

mysql> select * from room LEFT JOIN roomLock USING (roomNum) LEFT JOIN roomOwner USING (roomNum)
-> ;
+---------+--------------+----------+---------+---------+---------+-----------+
| roomNum | roomCapacity | roomType | roomNum | lockNum | roomNum | ownerName |
+---------+--------------+----------+---------+---------+---------+-----------+
| L1.4 | 10 | tute | L1.4 | K1 | NULL | NULL |
| L1.4 | 10 | tute | L1.4 | K1.4 | NULL | NULL |
| L1.6 | 26 | lab | L1.6 | k1.6 | NULL | NULL |
+---------+--------------+----------+---------+---------+---------+-----------+
3 rows in set (0.01 sec)

*************************************************************************************************
I would expect the same result if I Left join the roomOwner followed by roomNum, BUT IT IS NOT
Is it a bug?? or my expectation is wrong??
*************************************************************************************************
mysql> select * from room LEFT JOIN roomOwner USING (roomNum) LEFT JOIN roomLock USING (roomNum)
-> ;
+---------+--------------+----------+---------+-----------+---------+---------+
| roomNum | roomCapacity | roomType | roomNum | ownerName | roomNum | lockNum |
+---------+--------------+----------+---------+-----------+---------+---------+
| L1.4 | 10 | tute | NULL | NULL | NULL | NULL |
| L1.6 | 26 |lab | NULL | NULL | NULL | NULL |
+---------+--------------+----------+---------+-----------+---------+---------+
2 rows in set (0.00 sec)

****************************************************************************************************
But If I use "ON EXPR" it WORKS, under http://www.mysql.com/doc/en/JOIN.html

A LEFT JOIN B USING (C1,C2,C3,...)

is defined to be semantically identical to an ON expression like this:

A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,...


****************************************************************************************************
mysql> select * from room LEFT JOIN roomOwner ON room.roomNum = roomOwner.roomNum
-> LEFT JOIN roomLock ON room.roomNum = roomLock.roomNum;
+---------+--------------+----------+---------+-----------+---------+---------+
| roomNum | roomCapacity | roomType | roomNum | ownerName | roomNum | lockNum |
+---------+---------------------+-------------+-------------+---------------+-------------+------------+
| L1.4 | 10 | tute | NULL | NULL | L1.4 | K1 |
| L1.4 | 10 | tute | NULL | NULL | L1.4 | K1.4 |
| L1.6 | 26 | lab | NULL | NULL | L1.6 | k1.6 |
+---------+--------------------+-------------+-------------+---------------+-------------+-------------+


Thanks
Vinita


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