We would like to perform a left outer join on 5 tables, i.e., we want to create a view to include all the observations that are in the primary table, no matter whether there are matches in the other 4 or not. For example, let's say ID in the following tables is the KEY, and is the only field in each table, except in Table2.

Table1.ID: 1 2 3 4 5
Table2.ID: 1 1 Table2.Field_A: A B
Table3.ID: 2 5 9
Table4.ID: 1 2
Table5.ID: 4 8 10

Then, the view should have
Table1.ID Table2.ID (Field_A) Table3.ID Table4.ID Table5.ID
1 1(A) 1
1 1(B) 1
2 2 2
3
4 4
5 5

We know that one way to do it is to use UNION couple times, which will make the statement very long because we have many fields in each table. I would like to know if there is any easy way to do it as in Microsoft Access, or Oracle?

Thanks in advance.


CCF