Re: Outer Join-Problem

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 5 May 2003 08:49:11 -0700
Message-ID: <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.
> > > Thanx,
> > > Crypti
Received on Mon May 05 2003 - 17:49:11 CEST

Original text of this message