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

On Nov 4, 2:38 am, Ken <zwadcut..._at_yahoo.com> wrote:
> 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
> /

Ken,

Thanks for providing the DDL and the DML to set up the test case.

I am not too fond of the newer ANSI syntax, I prefer the older Oracle specific syntax. My first attempt was this: SELECT
  Z.COLB,
  X.COLB
FROM
  X,
  Y,
  Z
WHERE
  Z.COLB=X.COLB(+)
  AND X.COLA=Y.COLA(+)
  AND Y.COLC(+)='P'
ORDER BY
  Z.COLB; The output looks like this:
COLB COLB
---- ----
A A
B B
C C
D D
E E
F F
G
H

Note that C C appears, even though the second C should have been omitted due to the lack of a 'C' value in table Y. Correcting this issue required first directly joining tables X and Y, and then outer joining the result with table Z. I accomplished this with an inline view as follows:
SELECT
  Z.COLB,
  XY.COLB
FROM
  (SELECT
    X.COLB
  FROM
    X,
    Y
  WHERE
    X.COLA=Y.COLA
    AND Y.COLC='P') XY,
  Z
WHERE
  Z.COLB=XY.COLB(+)
ORDER BY
  Z.COLB; The output looks like this:
COLB COLB
==== ====
A A
B B
C
D D
E E
F F
G
H

I suggest that you try an ANSI syntax translation of the above.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Nov 04 2008 - 06:58:36 CST