Bob,
It is possible to do this with outer joins of views that are outer joins.
The cleanest way I have found to do this is with the UNION SELECT
syntax rather than the OUTER JOIN syntax.
However, with more than 2 tables, you have to consider many more
possible joins than with 2. 2 tables can give you links between the
two, plus rows from table 1 with no matches in table 2, and rows from
table 2 with no matches in table 1. With 3 tables, you have the 3-table
linked set, plus 3 possible combinations of 2 tables with no match in
the third table, plus 3 possible 1-table sets with no matches in either of
the other two, for a potential of 7 SELECT commands stacked up.
Of course, one or more of those combinations may not be possible in
your own joins, depending on constraints and business rules.
With 4 tables, you have way to many possible links, and you probably
have painted yourself into a corner with bad data design.
Make sure the first SELECT selects real columns from real tables, not
NULLS, because the first SELECT sets the datatypes for the entire
combined set.
Here's a sample that does a 3-table join, followed by two 2-table joins,
followed by rows from the middle table with no matches in either of the
other two.
SELECT t1.col1, t2.col2, t2.col3, t3.col4, t3.col5 +
FROM table1 t1, table2 t2, table3 t3 +
WHERE (t1.linkcol1 = t2.linkcol1) +
AND (t2.linkcol2 = t3.linkcol2) +
UNION ALL SELECT +
t1.col1, t2.col2, t2.col3, NULL, NULL +
FROM table1 t1, table2 t2 +
WHERE (t1.linkcol1 = t2.linkcol1) +
AND NOT EXIST +
(SELECT * from table3 t3 +
WHERE t3.linkcol2 = t2.linkcol2) +
UNION ALL SELECT +
NULL, t2.col2, t2.col3, t3.col4, t3.col5 +
FROM table2 t2, table3 t3 +
WHERE (t2.linkcol2 = t3.linkcol2) +
AND NOT EXIST +
(SELECT * from table1 t1 +
WHERE t1.linkcol1 = t2.linkcol1) +
UNION ALL SELECT +
NULL, t2.col2, t2.col3, NULL, NULL +
FROM table2 t2 +
WHERE NOT EXIST +
(SELECT * from table1 t1 +
WHERE t1.linkcol1 = t2.linkcol1) +
AND NOT EXIST +
(SELECT * from table3 t3 +
WHERE t3.linkcol2 = t2.linkcol2)
Bill
On Thu, 28 Jun 2001 16:09:08 -0400, Bob Powell wrote:
>I think I already know the answer to this one but I want
>to be sure. Is it possible to use an OUTER JOIN with more
>than two table/views at a time?
