I'm not sure how to describe my table structure, so hope this makes sense...
I have 3 tables in hierarchical relationship such that A has a one to many relationship to B which in turn has a one to many relationship with C. The trick is that the foreign key in B and C are allowed to be null (i.e. no parent defined). I also have D and E with no relation to A, B or C (directly).
Finally, I have F which is a join table with many to one relationships with C, D and E. None of its fields (FKs to the other tables) are nullable.
I would like to write a SQL statement that joins all the tables in a single result set. I know I have to user outer joins because I want all of A returned regardless of whether or not it has children in B and similar with B and C.
Question One: I have been looking at the ANSI outer join syntax (I've only used Oracle "(+)" before) and cannot find an example that outer joins more than 2 tables. Can someone provide/point to an example?
Question Two: Is it possible to include records from tables D and E based on the join table F? If so, is this done with outer joins?
Thanks!
EDIT
Of course, right after I post this, I found an example that answers question 1. However, question 2 still has me stumped.
Example:
SELECT A.a,
B.b,
C.c
FROM A
FULL OUTER JOIN B ON B.a = A.a
FULL OUTER JOIN C ON C.b = B.b