Re: Outer Join-Problem

ninjadesign_at_gmx.de (Crypti) wrote in message news:<c5df9e15.0304300505.141ddea0_at_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,
> Crypti
Received on Thu May 01 2003 - 18:11:57 CEST