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
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only