Newsgroups: bit.listserv.sqlinfo
Date:         Wed, 26 Aug 1992 14:10:12 -0600
From:         Stellar Systems Group <STELLAR@BCSC02.BITNET>
Subject:      Outer Join

 On Mon, 24 Aug 92 12:00:04 EDT Baerbel Allingham said:

 >We want to join all fields of 3 or more tables that match on a specific
 >number. It is possible that a number may not have a row in one of the
 >tables. In that case we would want a results table with all fields
 >fields of the table(s) where there was no match.
 >I guess it is called a full outer join.
 >
 >The results table would look like:
 >
 >t1n    t1f1  t1f2 ... t2n   t2f1  t2f2  ... t3numb  t3f1  t3f2 ....
 >---------------------------------------------------------------
 >1234   aaaa   12      1234  56      b       1234     98    abc
 >1234   aaaa   12      1234  56      b       1234     66    abc
 >5678   abab   45      .     .       .       5678     12    ddd
 >5678   abab   45      .     .       .       5678     08    ddd
 >4321   dddd   33      4321  2       e       .        .     .
 >
 >How could this be achieved, we run SQL/DS vers 3.3.

 The note said "It is possible that a number may not have a row in one of
 the tables."  I decided to expand that to one or more of the tables, and
 end up with the following query when attempting to do the outer join for
 three tables.  You can see that I end up referencing 21 tables.  I'm not
 sure about the internal limits for SQL/DS 3.3, but in 2.2 you could only
 reference 32 tables, views and internal tables in a single query.  This
 means that while I can write this ugly query for a three way join, I'd
 never get it to work for a four (or more) way join.

    SELECT a.col1, b.col2, c.col3
    FROM  tab1 a, tab2 b, tab3 c
    WHERE a.col1 = b.col1
    AND   b.col1 = c.col1
  UNION
    SELECT ' ', b.col2, c.col3
    FROM  tab2 b, tab3 c
    WHERE b.col1 = c.col1
    AND   NOT EXISTS ( SELECT 1 FROM tab1 a
                       WHERE a.col1 = b.col1 )
  UNION
    SELECT a.col1, ' ', c.col3
    FROM  tab1 a, tab3 c
    WHERE a.col1 = c.col1
    AND   NOT EXISTS ( SELECT 1 FROM tab2 b
                       WHERE b.col1 = a.col1 )
  UNION
    SELECT a.col1, b.col2, ' '
    FROM  tab1 a, tab2 b
    WHERE a.col1 = b.col1
    AND   NOT EXISTS ( SELECT 1 FROM tab3 c
                       WHERE c.col1 = a.col1 )
  UNION
    SELECT ' ', ' ', c.col3
    FROM  tab3 c
    WHERE NOT EXISTS ( SELECT 1 FROM tab1 a
                       WHERE a.col1 = c.col1 )
    AND   NOT EXISTS ( SELECT 1 FROM tab2 b
                       WHERE b.col1 = c.col1 )
  UNION
    SELECT ' ', b.col2, ' '
    FROM  tab2 b
    WHERE NOT EXISTS ( SELECT 1 FROM tab1 a
                       WHERE a.col1 = b.col1 )
    AND   NOT EXISTS ( SELECT 1 FROM tab3 c
                       WHERE c.col1 = b.col1 )
  UNION
    SELECT a.col1, ' ', ' '
    FROM  tab1 a
    WHERE NOT EXISTS ( SELECT 1 FROM tab2 b
                       WHERE b.col1 = a.col1 )
    AND   NOT EXISTS ( SELECT 1 FROM tab3 c
                       WHERE c.col1 = a.col1 )

 Regards,
 John F. Griffin
 Stellar Systems Group
 306 - 1095 McKenzie Avenue
 Victoria, B.C.  Canada  V8P 2L5
 Phone:(604) 479-7722  Fax:(604) 479-6526
Comments: Gated by NETNEWS@AUVM.AMERICAN.EDU