Re: double outer join

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.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)