Re: Outer Join-Problem

afilonov_at_yahoo.com (Alex Filonov) wrote in message news:<336da121.0305050749.2bc45304_at_posting.google.com>...
> ninjadesign_at_gmx.de (Crypti) wrote in message news:<c5df9e15.0305050228.3ed3767a_at_posting.google.com>...
> > afilonov_at_yahoo.com (Alex Filonov) wrote in message news:<336da121.0305010811.6f658f7f_at_posting.google.com>...
> > > ninjadesign_at_gmx.de (Crypti) wrote in message news:<c5df9e15.0304300505.141ddea0@posting.google.com>...
> > > > Hi there. I have a problem with outer joins, maybe you can help me.
> > > >
> > > > Lets say, there is table A, B and C. Table B references to Table A
> > > > there is n:1-Relation between B and A. The same between C and A, C
> > > > references A and there is n:1-Relation between C and A.
> > > > Now I'm trying to get all the data from A ,the data from B and C
> > > > together. My problem is, that it's nessesary to get data everytime,
> > > > independent if there are datasets in B or C.
> > > > So I used outer join between A and B and A and C.
> > > > The problem is, that when using outer joins just between A-B and A-C
> > > > then I will get the cross product of B and C.
> > > >
> > > > Example:
> > > >
> > > > table A table B table C
> > > > -------- ---------- -----------
> > > >
> > > > col1 col2 col1 col2 col1 col2
> > > > ---- ----- ----- ---- ----- -----
> > > > x 1 a 2 a 1
> > > > y 2 b 2 b 1
> > > > z 3 c 3 c 3
> > > > d 3 d 3
> > > > e 3
> > > >
> > > > In this example col2 of table B and C references to col2 of A (assume
> > > > this is PK of A).
> > > >
> > > > OK, now to SQL. When I use the following SQL-Statement:
> > > >
> > > > SELECT A.col1 A, B.col1 B, C.col1 C from A, B, C where
> > > > A.col2=B.col2(+) and A.col2=C.col2(+);
> > > >
> > > > I will get the following result:
 

> > > >
> > > > A B C
> > > > - - -
> > > > x a
> > > > x b
> > > > y a
> > > > y b
> > > > z c c
> > > > z d c
> > > > z c d
> > > > z d d
> > > > z c e
> > > > z d e
> > > >
> > > > The first 4 lines are OK, that's what I want. The problem are the
> > > > lines starting with z. Is there a way to get the following result:
> > > >
> > > > A B C
> > > > - - -
> > > > x a
> > > > x b
> > > > y a
> > > > y b
> > > > z c c
> > > > z d d
> > > > z e
> > > >
> > > > Does anybody have an idea, how the SQL-Statement should look like to
> > > > get such a result?
> > > >
> > >
> > > Add a condition:
> > >
> > > and nvl(B.coll, '#') = nvl(C.coll, '#')
> > >
> > > # is just an example. Use any character which is not used in column coll in
> > > tables B and C.
> >
> > Thanx for reply, but when adding the condition I will just get
> >
> > z c c and
> > z d d.
> >
> > That's not what I wanted. I need to have:
> > A B C
> > -----
> > x a
> > x b
> > y a
> > y b
> > z c c
> > z d d
> > z e
> >
> > Is there maybe another way to get it?
> >
>
> Sorry, didn't read the question attentively. The added condition should be:
>
> and (b.col2 is null or c.col2 is null or b.col1 = c.col1)
>
> It won't give you
> A B C
> -----
>
> z e
>
> line. Looks like you are looking at the full outer join of B and C, outer
> joined to A. It's available in version 9i of Oracle only. I'll send you
> full syntax from home, don't have 9i here to test.
>
> HTH.
>

Here's the statement which works on 9.2. I hope you can replace table and column names:

select a.c, decode(a.n, d.bn, d.bc, null) c1,

            decode(a.n, d.cn, d.cc, null) c2 from ta1 a left outer join
 (select b.n bn, b.c bc, c.n cn, c.c cc
from ta2 b full outer join ta3 c on b.c = c.c) d on a.n = d.bn

                                                 or a.n = d.cn

Result:

C C1 C2
---------- ---------- ----------

x		      a
x		      b
y	   a
y	   b
z	   c	      c
z	   d	      d
z		      e

> > > > Thanx,
> > > > Crypti
Received on Tue May 06 2003 - 05:03:00 CEST