From: afilonov@yahoo.com (Alex Filonov) Newsgroups: comp.databases.oracle Subject: Re: Outer Join-Problem Date: 5 May 2003 08:49:11 -0700 Organization: http://groups.google.com/ Lines: 112 Message-ID: <336da121.0305050749.2bc45304@posting.google.com> References: <336da121.0305010811.6f658f7f@posting.google.com> NNTP-Posting-Host: 205.215.216.202 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1052149751 8843 127.0.0.1 (5 May 2003 15:49:11 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 5 May 2003 15:49:11 GMT ninjadesign@gmx.de (Crypti) wrote in message news:... > afilonov@yahoo.com (Alex Filonov) wrote in message news:<336da121.0305010811.6f658f7f@posting.google.com>... > > ninjadesign@gmx.de (Crypti) wrote in message news:... > > > 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