Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> double outer join

double outer join

From: Jed <djedziniak_at_hotmail.com>
Date: 12 Jun 2003 13:17:20 -0700
Message-ID: <d1233510.0306121217.3b46048a@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US