I have 2 almost identical SQL statements {copied except 1 is a LEFT join and
the other is an INNER join}.
The INNER join gives me values for all of the fields. The LEFT join gives me
NULL's for all of the prec_... {the LEFT join table} fields.
If the INNER JOIN gives me values and not an empty set then why does the LEFT
join give me NULL's?
I have included the satements, SELECT * and CREATE TABLE for the 3 tables.
I have run this on 5.0.15 and 5.0.17.
mysql> SELECT user.user_ID, sesn.user_ID, sesn.orgn_ID, prec3.orgn_ID,
-> prec3.prec_Type, prec3.prec_Level,prec3.prec_Value,
prec3.prec_ID,
-> if(isnull(prec3.prec_Replace_Level),
-> 'sesn',
-> prec3.prec_Replace_Level
-> ) as pl3
-> FROM users AS user
-> INNER JOIN sessions AS sesn
-> USING(user_ID
-> )
-> LEFT JOIN precedences AS prec3
-> ON (prec3.orgn_ID = sesn.orgn_ID
-> AND prec3.prec_Type = 'Phones'
-> AND prec3.prec_Level = 'user'
-> AND prec3.prec_Value = 'Primary'
-> AND prec3.prec_ID = 3
-> )
-> where sesn.sesn_ID = 1;
+---------+---------+---------+---------+-----------+------------+------------+---------+------+
| user_ID | user_ID | orgn_ID | orgn_ID | prec_Type | prec_Level | prec_Value |
prec_ID | pl3 |
+---------+---------+---------+---------+-----------+------------+------------+---------+------+
| AGB1 | AGB1 | AXIS | NULL | NULL | NULL | NULL
| NULL | sesn |
+---------+---------+---------+---------+-----------+------------+------------+---------+------+
1 row in set (0.02 sec)
mysql>
mysql>
mysql> SELECT user.user_ID, sesn.user_ID, sesn.orgn_ID, prec3.orgn_ID,
-> prec3.prec_Type, prec3.prec_Level,prec3.prec_Value,
prec3.prec_ID,
-> if(isnull(prec3.prec_Replace_Level),
-> 'sesn',
-> prec3.prec_Replace_Level
-> ) as pl3
-> FROM users AS user
-> INNER JOIN sessions AS sesn
-> USING(user_ID
-> )
-> INNER JOIN precedences AS prec3
-> ON (prec3.orgn_ID = sesn.orgn_ID
-> AND prec3.prec_Type = 'Phones'
-> AND prec3.prec_Level = 'user'
-> AND prec3.prec_Value = 'Primary'
-> AND prec3.prec_ID = 3
-> )
-> where sesn.sesn_ID = 1;
+---------+---------+---------+---------+-----------+------------+------------+---------+------+
| user_ID | user_ID | orgn_ID | orgn_ID | prec_Type | prec_Level | prec_Value |
prec_ID | pl3 |
+---------+---------+---------+---------+-----------+------------+------------+---------+------+
| AGB1 | AGB1 | AXIS | AXIS | phones | user | Primary
| 3 | locn |
+---------+---------+---------+---------+-----------+------------+------------+---------+------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT * FROM sessions;
+---------+--------------+--------------+---------+---------+---------+---------+---------+---------------------+---------------------+
| sesn_ID | perm_user_ID | perm_usgp_ID | user_ID | usgp_ID | acct_ID | locn_ID
| orgn_ID | sesn__Timestamp | sesn_Create |
+---------+--------------+--------------+---------+---------+---------+---------+---------+---------------------+---------------------+
| 1 | AGB1 | ADZZ | AGB1 | ADZZ | | AXIS
| AXIS | 2005-12-23 08:32:26 | 2005-12-23 08:30:02 |
| 2 | AGB1 | ADZZ | AGB1 | ADZZ | | AXIS
| AXIS | 2005-12-23 08:32:26 | 2005-12-23 08:30:07 |
+---------+--------------+--------------+---------+---------+---------+---------+---------+---------------------+---------------------+
2 rows in set (0.02 sec)
mysql>
mysql> SELECT * FROM users;
+---------+---------+---------+---------+--------------+------------------+-----------+------------+------------+------------+-------------+----------+---------------------+-------------+
| user_ID | orgn_ID | locn_ID | usgp_ID | prev_usgp_ID | user_Log_On_Name |
user_Pass | user_FName | user_LName | user_PName | user_Active | user_Who |
user_Timestamp | user_Create |
+---------+---------+---------+---------+--------------+------------------+-----------+------------+------------+------------+-------------+----------+---------------------+-------------+
| AGB1 | AXIS | AXIS | ADZZ | NULL | gbruce |
rgbjs1jc | Ralph | Bruce | Gordon | Yes | AGB1 |
2005-12-23 08:59:31 | NULL |
+---------+---------+---------+---------+--------------+------------------+-----------+------------+------------+------------+-------------+----------+---------------------+-------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT * FROM precedences;
+---------+-----------+------------+------------+---------+--------------------+--------------------+-------------+----------+---------------------+---------------------+
| orgn_ID | prec_Type | prec_Level | prec_Value | prec_ID | prec_Replace_Level
| prec_Replace_Value | prec_Active | prec_Who | prec_Timestamp |
prec_Create |
+---------+-----------+------------+------------+---------+--------------------+--------------------+-------------+----------+---------------------+---------------------+
| AXIS | phones | user | Primary | 1 | user
| Primary | Yes | AGB1 | 2005-12-23 09:01:11 |
2005-12-23 09:01:05 |
| AXIS | phones | user | Primary | 2 | acct
| Primary | Yes | AGB1 | 2005-12-23 09:01:24 |
2005-12-23 09:01:05 |
| AXIS | phones | user | Primary | 3 | locn
| Primary | Yes | AGB1 | 2005-12-23 09:02:04 |
2005-12-23 09:01:41 |
| AXIS | phones | user | Primary | 4 | orgn
| Primary | Yes | AGB1 | 2005-12-23 09:02:01 |
2005-12-23 09:01:56 |
+---------+-----------+------------+------------+---------+--------------------+--------------------+-------------+----------+---------------------+---------------------+
4 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE sessions;
| Table | Create Table
| sessions | CREATE TABLE `sessions` (
`sesn_ID` int(11) NOT NULL auto_increment,
`perm_user_ID` char(4) NOT NULL default '',
`perm_usgp_ID` char(4) NOT NULL default '',
`user_ID` char(4) NOT NULL default '',
`usgp_ID` char(4) NOT NULL default '',
`acct_ID` char(4) NOT NULL default '',
`locn_ID` char(4) NOT NULL default '',
`orgn_ID` char(4) NOT NULL default '',
`sesn__Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`sesn_Create` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`sesn_ID`),
KEY `perm_user_ID_IDX` (`perm_user_ID`,`sesn_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE users;
| Table | Create Table
| users | CREATE TABLE `users` (
`user_ID` char(4) NOT NULL default '',
`orgn_ID` char(4) NOT NULL default '',
`locn_ID` char(4) NOT NULL default '',
`usgp_ID` char(4) NOT NULL default '',
`prev_usgp_ID` char(4) default NULL,
`user_Log_On_Name` char(50) NOT NULL default '',
`user_Pass` char(50) NOT NULL default '',
`user_FName` char(50) NOT NULL default '',
`user_LName` char(50) NOT NULL default '',
`user_PName` char(50) NOT NULL default '',
`user_Active` enum('Yes','No') NOT NULL default 'Yes',
`user_Who` char(4) NOT NULL default '',
`user_Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`user_Create` datetime default NULL,
PRIMARY KEY (`user_ID`),
UNIQUE KEY `user_Log_On_Name_IDX` (`user_Log_On_Name`),
KEY `usgp_ID_IDX` (`usgp_ID`),
KEY `user_locn_ID_IDX` (`locn_ID`),
KEY `orgn_idx` (`orgn_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
1 row in set (0.02 sec)
mysql> SHOW CREATE TABLE precedences;
| Table | Create Table
| precedences | CREATE TABLE `precedences` (
`orgn_ID` char(8) NOT NULL default '',
`prec_Type` enum('phones','addresses','emails','web') NOT NULL default
'addresses',
`prec_Level` enum('user','acct','locn','orgn') NOT NULL default 'orgn',
`prec_Value` char(20) NOT NULL default 'Primary',
`prec_ID` smallint(6) NOT NULL default '0',
`prec_Replace_Level` enum('user','acct','locn','orgn') NOT NULL default
'orgn',
`prec_Replace_Value` char(20) NOT NULL default 'Primary',
`prec_Active` enum('Yes','No') NOT NULL default 'Yes',
`prec_Who` char(8) NOT NULL default '',
`prec_Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`prec_Create` datetime NOT NULL,
PRIMARY KEY (`orgn_ID`,`prec_Type`,`prec_Level`,`prec_Value`,`prec_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
