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).

In reply to Re: Re: Re: Re: So you think you're good with structures? by clintp
in thread So you think you're good with structures? by clintp

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.