Outer Join with an extra condition
Date: Thu, 5 Jun 2008 03:22:29 -0700 (PDT)
Message-ID: <2b50e739-b34a-4331-8de7-f680c6ec157f@t12g2000prg.googlegroups.com>
I am taking a left outer join to which I would like to add a condition.
create table tmp_nani_student(stid number) ;
insert into tmp_nani_student values(1) ; insert into tmp_nani_student values(2) ; insert into tmp_nani_student values(3) ; insert into tmp_nani_student values(4) ;
create table tmp_nani_marks(stid number, marks number, grade number) ;
insert into tmp_nani_marks values(1, 51, 3) ; insert into tmp_nani_marks values(2, 71, 2) ; insert into tmp_nani_marks values(4, 81, 1) ; insert into tmp_nani_marks values(5, 55, 3) ;commit ;
Then I run :
select a.stid, b.marks, b.grade
from tmp_nani_student a, tmp_nani_marks b
where a.stid = b.stid (+) and (b.grade < 3 or not exists (select
c.stid from tmp_nani_marks c where a.stid = c.stid) )
So far so good.
But then I add one more row to the second table:
insert into tmp_nani_marks values(2, 17, null) ;
Now I want to run a query which would exclude this new row where grade=null. If the outer join fetches rows with grade=null, that is fine. But I don't want to fetch records where grade is explicitly set to null.
Run again:
select a.stid, b.marks, b.grade
from tmp_nani_student a, tmp_nani_marks b
where a.stid = b.stid (+) and (b.grade < 3 or not exists (select
c.stid from tmp_nani_marks c where a.stid = c.stid) )
But the record (2,17, null) shows up. I don't want it to show up. What would be the query for it?
TIA. Received on Thu Jun 05 2008 - 05:22:29 CDT