[thelist] Nested Queries

[Steve Lewis]

rudy wrote:
> no thanks, but thanks for asking  ;o)
bummer :)

> can you explain in words what that INSERT is doing?
WHILE (@@rowcount > 0) BEGIN
   INSERT #org_chart (chartdepth, affiliate, parent_affiliate)
   SELECT DISTINCT o1.chartdepth+1, o2.affiliate, o1.parent_affiliate
   FROM #org_chart o1 JOIN #org_chart o2 ON
        (o1.affiliate=o2.parent_affiliate)
   WHERE o1.chartdepth=(SELECT MAX(chartdepth) FROM #org_chart)
   AND o1.parent_affiliate<>o1.affiliate
END

this one?

> extra rows linking all lower level nodes to every higher level in their
> subtree?
yes, basically.  This INSERT is effectively a cross join for a tree
structure. :)  As I tried to say before, it enumerates all the explicit
AND implicit relationships in the hierarchy.  (IE it says specifically
that your mother is your ancestor and your grandmother is your ancestor
and your grandmohter is your mother's ancestor given the knowledge that
your mother is your ancestor and your grandmother is your mother's ancestor)

> the chartdepth column looks like it might be used in MLM  ;o)
Henderson's example was generating an org chart: illustrating a
hierarchy of leadership in a business (who answers to whom).  We use it
for a reseller network.  I suppose that is almost the same thing! :)

-- Steve