Re: transitive closure code and SQL wanted

Dear Joe,

        Thanks for posting the SQL to do the query. However the hard part of this approach is the code that sets the correct values of left and right in the table. That is what I meant by "code" wanted.

Thanks to some other posters for useful references. My tree is relatively small and relatively static, so I can update it it all at once, rather than incrementally.

71062.1056_at_compuserve.com (--CELKO--) writes: ...
> If you know the nested sets model, this is easy:
...
> SELECT O1.emp AS boss, O2.emp AS subordinate,
> COUNT(O2.lft) AS distance
> FROM OrgChart AS O1, OrgChart AS O2
> WHERE O2.lft BETWEEN O1.lft AND O1.rgt
> GROUP BY O1.emp;

Steve

-- 
Steven Tolkin          steve.tolkin_at_fmr.com      617-563-0516 
Fidelity Investments   82 Devonshire St. V8D     Boston MA 02109
There is nothing so practical as a good theory.  Comments are by me, 
not Fidelity Investments, its subsidiaries or affiliates.