Re: outer join... please HELP!!!!

From: Dave Merrill <dmerrill_at_oracle.com>
Date: 18 Apr 94 22:01:47 GMT
Message-ID: <DMERRILL.94Apr18140147_at_ap227sun.oracle.com>

In article <jmboivin.12.00115355_at_cti.ulaval.ca> jmboivin_at_cti.ulaval.ca (Jean-Marc Boivin) writes:

   Newsgroups: comp.databases.oracle
   Path: oracle!gatekeeper.us.oracle.com!sgiblab!swrinde!gatech!newsxfer.itd.umich.edu!nntp.cs.ubc.ca!utcsri!newsflash.concordia.ca!CC.UMontreal.CA!IRO.UMontreal.CA!clouso.crim.ca!athena.ulaval.ca!jmboivin    From: jmboivin_at_cti.ulaval.ca (Jean-Marc Boivin)    Keywords: sql
   Lines: 52
   Sender: news_at_athena.ulaval.ca
   Nntp-Posting-Host: boj.cti.ulaval.ca
   Organization: Universite Laval (CTI)
   X-Newsreader: Trumpet for Windows [Version 1.0 Rev Final Beta #10]    Date: Thu, 14 Apr 1994 22:19:24 GMT

   Hi!,

   I have a problem with a complex outer join:

   I have 4 tables like that:

   table1

      key_table1

   table2

      key_table2
      key_table1   /* foreigh key on table 1 */

   table3
      key_table3
      key_table2   /* foreign key on table 2 */

   table4
      key_table4
      key_table3   /* foreign key on table 3 /*

   I want to build a SQL statement which will produce a    list of ALL record in table 1 and, if there is a related record    in table 4, will print the key value of table 4.

   Ex:

   key_table1            key_table4
   -------------         ---------------
   1
   2                     50    /* oups, their is a related record in table4 */
   3                     25

   4
   5

   6                     30

   and so...

   I tried with outer join but i don't understand how to use it    when your join is on more than 2 tables.

   Please HELP!!!!

   Thank's

---

   Jean-Marc Boivin
   universite Laval
   Quebec, PQ
   Canada
   (418) 656 3632
   jmboivin_at_cti.ulaval.ca

---

Solution:

select

	T1.key_table1,
	T4.key_table1
from
	table1	T1,
	table2	T2,
	table3	T3,
	table4	T4
where
	T2.key_table1 (+)	= T1.key_table1
and
	T3.key_table2 (+)	= T2.key_table2
and
	T4.key_table3 (+)	= T3.key_table3;

--
Thanks much -

========================================================================
   David S. Merrill                                500 Oracle Parkway   
   Product Manager                                         Box 659305   
   Oracle Order Entry                       Redwood Shores, CA  94065   
                                                                        
   Voice  (415) 506 2084                                   300 OP 546   
   FAX    (415) 506 7294                       dmerrill_at_us.oracle.com   
========================================================================

Received on Tue Apr 19 1994 - 00:01:47 CEST