3 table outer join with where caluse colc='P' question

From: Ken <zwadcutter_at_yahoo.com>
Date: Mon, 3 Nov 2008 23:38:22 -0800 (PST)
Message-ID: <08f83653-d5c3-4a50-858d-cc6cd8e6761b@f40g2000pri.googlegroups.com>

I have distilled my question down to this. I have three table and I normally query tables x and y to get my passed results something like

select 	x.cola,x.colb,y.colc
from 	x,y
where 	x.cola=y.cola
and	y.colc = 'P';

      COLA COLB  C
---------- ----- -
         1 A     P
         2 B     P
         4 D     P
         5 E     P
         6 F     P

Now I want to have a table with all possible values of x.colb and then do an outer joing to get output like

I would like the output to look like

z.colc	x.colb
A	A
B	B
C
D	D
E	E
F	F

G
H

So i know that I don't have passed results form C,G,H everything works as expected if I don't have the where clause y.colC='P'

this is the query I have so far but I can't get the where clause y.colc='P' to work correctly

select 	z.colb,x.colb
from  	z left outer join x on z.colb=x.colb
	  left outer join y on x.cola=y.cola  and y.colc='P'

order by z.colb
/

drop table x;
drop table y;
drop table z;

create table x (cola number, colb varchar2(1));

create table y (cola number, colc varchar2(1)); create table z (colb varchar2(1));

insert into x values (1,'A');
insert into x values (2,'B');
insert into x values (3,'C');
insert into x values (4,'D');
insert into x values (5,'E');
insert into x values (6,'F');


insert into y values (1,'P');
insert into y values (2,'P');
insert into y values (3,'F');
insert into y values (4,'P');

insert into y values (5,'P');
insert into y values (6,'P');

insert into Z values ('A');
insert into Z values ('B');
insert into Z values ('C');
insert into Z values ('D');
insert into Z values ('E');
insert into Z values ('F');
insert into Z values ('G');
insert into Z values ('H');

commit; Received on Tue Nov 04 2008 - 01:38:22 CST