Re: Outer Join with an extra condition

From: <dn.perl_at_gmail.com>
Date: Fri, 6 Jun 2008 22:20:20 -0700 (PDT)
Message-ID: <f0516c28-e874-4467-aaf2-ceb1ba730001@p39g2000prm.googlegroups.com>


On Jun 5, 9:37 am, DA Morgan <damor..._at_psoug.org> wrote:
> dn.p..._at_gmail.com wrote:
> > 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.
>
> AND <column_name> IS NOT NULL;
>

Yes, figured it out soon after I posted my problem and could then sleep peacefully. But I was sure somebody would help me out. Thanks.

The trick is to get the 'placement' of that clause (and column_name is not null) correct, and simple as it seems to me now, I didn't get it right at once. Received on Sat Jun 07 2008 - 00:20:20 CDT

Original text of this message