Re: Outer Join-Problem
Date: 5 May 2003 03:28:40 -0700
Message-ID: <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?
> > Thanx,
> > Crypti
Received on Mon May 05 2003 - 12:28:40 CEST