Hi Rick, First, the MySQL IsNULL() function does not operate like the ORACLE or MS SQL version. It is merely a test and returns either 1 or 0. You will need to use COALESCE() to provide a non-null replacement for a null value.
I am not sure what you are trying to accomplish with your EXISTS() clause but that was what was killing you. I tried it both as EXISTS and as NOT EXISTS and I didn't get *any* records where rangeid=4. I can't fully explain the behavior but I think its failure is linked to the fact that there are zero b table rows for rangeid of 4. I rewrote your exclusion as another LEFT JOIN and I have the right number of rows but I need you to verify the results: select a.id id, a.trandate trandate, a.acct acct, a.rateplan rateplan, a.rangeid rangeid, case coalesce(a.rangeid, 99999) when 99999 then 'null rangeid' else coalesce(b.descr, 'null descr') end descr from transactions a left outer join ref_info b on a.rateplan = b.rateplan and a.rangeid = b.rangeid and a.trandate > b.effdate left join ref_info c on c.rateplan = b.rateplan and c.rangeid = b.rangeid and c.effdate < a.trandate and c.effdate > b.effdate where a.acct =123 and c.rateplan is null; Let me know if this worked as you wanted. I will keep trying to figure out why that EXISTS clause failed.... Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Rick Robinson" <[EMAIL PROTECTED]> To: "Mysql" <[EMAIL PROTECTED]> cc: 06/11/2004 12:48 Fax to: PM Subject: Possible problem with outer join in 4.1.2 Please respond to rick Hi all- I'm not certain if this is a bug in MySQL, a bug in Oracle, or a possible miscoding of my outer join, but I have a scenario in which I've replicated a table set up and query from an Oracle application and I'm not getting the same result set. The following script sets up the representative tables and data and has the query. drop table if exists ref_info; drop table if exists transactions; create table ref_info ( rateplan char(3) not null, rangeid int not null, effdate datetime not null, descr char(20) not null, primary key(rateplan, rangeid, effdate) ) type=myisam ; create table transactions ( id int not null auto_increment, acct int not null, rateplan char(3) not null, trandate datetime not null, rangeid int null, primary key(id), key(acct, rateplan, trandate) ) type=myisam ; insert into ref_info values ('aaa',1,'1970-01-01','aaa:1:1970-01-01'), ('aaa',1,'2004-06-01','aaa:1:2004-06-01'), ('aaa',1,'2004-06-03','aaa:1:2004-06-03'), ('aaa',1,'2004-06-05','aaa:1:2004-06-05'), ('aaa',2,'1970-01-01','aaa:2:1970-01-01'), ('aaa',2,'2004-06-01','aaa:2:2004-06-01'), ('aaa',2,'2004-06-03','aaa:2:2004-06-03'), ('aaa',2,'2004-06-05','aaa:2:2004-06-05'), ('aaa',3,'1970-01-01','aaa:3:1970-01-01'), ('aaa',3,'2004-06-01','aaa:3:2004-06-01'), ('aaa',3,'2004-06-03','aaa:3:2004-06-03'), ('aaa',3,'2004-06-05','aaa:3:2004-06-05'), ('aaa',5,'1970-01-01','aaa:5:1970-01-01'), ('aaa',5,'2004-06-01','aaa:5:2004-06-01'), ('aaa',5,'2004-06-03','aaa:5:2004-06-03'), ('aaa',5,'2004-06-05','aaa:5:2004-06-05') ; insert into transactions values (null,123,'aaa','2004-05-30',1), (null,123,'aaa','2004-06-02',1), (null,123,'aaa','2004-06-04',1), (null,123,'aaa','2004-06-06',1), (null,123,'aaa','2004-05-30',2), (null,123,'aaa','2004-06-02',2), (null,123,'aaa','2004-06-04',2), (null,123,'aaa','2004-06-06',2), (null,123,'aaa','2004-05-30',3), (null,123,'aaa','2004-06-02',3), (null,123,'aaa','2004-06-04',3), (null,123,'aaa','2004-06-06',3), (null,123,'aaa','2004-05-30',4), (null,123,'aaa','2004-06-02',4), (null,123,'aaa','2004-06-04',4), (null,123,'aaa','2004-06-06',4), (null,123,'aaa','2004-05-30',5), (null,123,'aaa','2004-06-02',5), (null,123,'aaa','2004-06-04',5), (null,123,'aaa','2004-06-06',5), (null,123,'aaa','2004-05-30',null), (null,123,'aaa','2004-06-02',null), (null,123,'aaa','2004-06-04',null), (null,123,'aaa','2004-06-06',null) ; select count(*) from transactions ; select a.id id, a.trandate trandate, a.acct acct, a.rateplan rateplan, ifnull(a.rangeid, 99999) rangeid, case ifnull(a.rangeid, 99999) when 99999 then 'null rangeid' else ifnull(b.descr, 'null descr') end descr from transactions a left outer join ref_info b on a.rateplan = b.rateplan and a.rangeid = b.rangeid and a.trandate > b.effdate where a.acct = 123 and not exists (select 1 from ref_info c where c.rateplan = b.rateplan and c.rangeid = b.rangeid and c.effdate < a.trandate and c.effdate > b.effdate ) order by 2 desc, 1 desc ; On the nifty outer join query, I expect to get 24 rows back, but only get 16; all rows with either a null a.rangeid or a.rangeid=4 are missing. This (effectively) same query works in Oracle and returns all data. (as FYI, the query is trying to get back descriptions that are effective dated - however, some descriptions may not be there for various reasons). So, am I writing this query incorrectly for MySQL? Is this a possible bug? Thanks for any help. But please skip suggestions on trying to make changes to the schema definition. Running MySQL 4.1.2 binary on both Solaris 2.9 and WinXP. Best regards, Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]