Re: double outer join

Guys this wasn't homework, I know JED and he's under the gun to get some things done at work so I suggested he post his questions here. It would be great if you could give him a hand instead of treating him like some college kid.

Thanks
Scott

Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3EE8E517.1785C15_at_exxesolutions.com>...
> Jed wrote:
>
> > My dataset looks like this
> >
> > CIID RESULT NUM
> > A PASS 1
> > A FAIL 2
> > B PASS 1
> > C FAIL 2
> > D PASS 3
> > D FAIL 3
> >
> > I want to query this data and return a dataset that looks like this
> >
> > CIID PASS FAIL
> > A 1 2
> > B 1 0
> > C 0 2
> > D 3 3
> >
> > I tried this (table name is xxx):
> >
> > select pass.ciid, pass.num, fail.num
> > from (select ciid,num from xxx where result='PASS') pass,
> > (select ciid,num from xxx where result='FAIL') fail
> > where pass.ciid=fail.ciid
> >
> > I lose the B and C record
> >
> > When I tried this:
> > select pass.ciid, pass.num, fail.num
> > from (select ciid,num from xxx where result='PASS') pass,
> > (select ciid,num from xxx where result='FAIL') fail
> > where pass.ciid=fail.ciid(+)
> >
> > I still lose the C record
> >
> > Is there a way to do something like a double outer join
> > pass.ciid (+) = fail.ciid (+)
> >
> > When I try, I get the error ORA-01468: a predicate may reference
> > only one outer-joined table
>
> This is classroom homework so I'll give you a hint rather than the
> answer.
>
> You are on the right track but think about NVL and how you might use it.
Received on Fri Jun 13 2003 - 10:04:23 CDT