Outer Join-Problem

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?

Thanx,
Crypti Received on Wed Apr 30 2003 - 15:05:13 CEST