From: ninjadesign@gmx.de (Crypti)
Newsgroups: comp.databases.oracle
Subject: Re: Outer Join-Problem
Date: 5 May 2003 03:28:40 -0700
Organization: http://groups.google.com/
Lines: 94
Message-ID: <c5df9e15.0305050228.3ed3767a@posting.google.com>
References: <c5df9e15.0304300505.141ddea0@posting.google.com> <336da121.0305010811.6f658f7f@posting.google.com>
NNTP-Posting-Host: 62.153.200.205
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1052130520 26161 127.0.0.1 (5 May 2003 10:28:40 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 5 May 2003 10:28:40 GMT


afilonov@yahoo.com (Alex Filonov) wrote in message news:<336da121.0305010811.6f658f7f@posting.google.com>...
> ninjadesign@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