Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> double outer join
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 Received on Thu Jun 12 2003 - 15:17:20 CDT
![]() |
![]() |