Ummm...Yeah I think. Look at the data, it pretty-much speaks for itself but lemme try an english explanation. If this confuses someone, ignore it and go back to the data. :)
The simple setup
Let's simplify the explanation first. Let's say there's only a 1:1 relationship between records (for example). Starting with the first record, descend into each child table and pick up the record that's referred to by key.
So if your diagram above is the tables and how they relate to each other, we'd take the data from the record in A, the record from B, the record from C, the record from D and the record from E and accumulate those into a single output record.
On the next record in A, we'd traverse down into B, C, D, and E and grab the matching record from each of those and make another output record. And so on.
2 records in A would yeild 2 output records after joining together all of the child tables. This is because everything had a 1:1 relationship.
Now, it gets more complex
What if a table could have more than one matching record? (more than one child for a given parent) In my example, starting with "table a" and record "aa" if you follow the chain down you'll find that a parent in 'table c' can have a 1:n relationship with 'table d'.
So you traverse all of the tables as you did in the simple example, but having reached table D you take only one of the matching records. That makes an output record.
Do it again but take the other record from D, and that gives another output record. The 2:1 relationship between D and C gives you 2 records for each entry in A.
Now take the 'bb' start record from 'table a' above. Traversing it, you'll find a 2:1 relationship in both tables 'e' and 'd'. This means that for the start record 'bb' in 'table a' you wind up with 4 permutations on the output.
A simple recursion won't do, becuase you have to know when you've encountered a list to recurse into and at each node of the list stop and dump the results so-far. And the 1:n relationship between the records isn't known until you're at that node (no fair note-taking about the table's properties).
|