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

On Nov 4, 7:58 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.

Which would work out to be something like: select colb, foo
from z left outer join (select cola, colb, colb as foo from x

                           inner join (select * from y where
colc='P')
                           using (cola))
          using (colb)

8 rows selected. Received on Tue Nov 04 2008 - 08:54:34 CST