Re: Nested Model

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Joe Celko (joe.celko_at_northface.edu)
Date: 02/19/04 

Date: Thu, 19 Feb 2004 15:50:40 -0800

>> Nothing like a little shameless plug <<

Well, I cannot afford a banner spot on DevDex ...

>> When using the BETWEEN criteria for the nested model, we are not
querying for the UserID's the manager manages, but the node id of the
tree. <<

Actually, you are querying for the entire **subtree** that is rooted at
the node in the case of a known employee and all their Supervisors, no
matter how deep the tree.

SELECT O2.*
   FROM OrgChart AS O1, OrgChart AS O2
  WHERE O1.lft BETWEEN O2.lft AND O2.rgt
    AND O1.emp = @myemployee;

The employee and all his subordinates as a nice symmetry to it.

SELECT O1.*
   FROM OrgChart AS O1, OrgChart AS O2
  WHERE O1.lft BETWEEN O2.lft AND O2.rgt
    AND O2.emp = @myemployee;

After you get the positons in the Organizational Chart, you join it to
the Personnel table to find them.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Help with ROW_NUMBER and recursive query
    ... Stop trying to force nested sets - it doesn't scale anywhere near the other solutions we have now built into the product. ... Let us define a simple OrgChart table like ... boss_emp_name and employee columns are the same kind of thing (i.e. ... An employee and all their Supervisors, no matter how deep the tree. ...
    (microsoft.public.sqlserver.programming)
  • Re: Complex SQL problem
    ... >The usual example of a tree structure in SQL books is ... >(emp CHAR(10) NOT NULL PRIMARY KEY, ... >simple OrgChart table like this, ... >employee and one for his boss in the textbooks. ...
    (microsoft.public.access.formscoding)
  • Re: Complex SQL problem
    ... CREATE TABLE OrgChart ... SQL is a set oriented language, this is a better model than the usual ... lft and rgt columns is called the adjacency list model, ... To show a tree as nested sets, replace the emps with ovals, then nest ...
    (microsoft.public.access.formscoding)
  • Re: Help with ROW_NUMBER and recursive query
    ... There are many ways to represent a tree or hierarchy in SQL. ... Let us define a simple OrgChart table like ...
    (microsoft.public.sqlserver.programming)
  • Re: Get all Parent Child data of a table
    ... define a simple OrgChart table like this. ... Another problem with the adjacency list model is that the ... To show a tree as nested sets, replace the nodes with ovals, and then ... Here is version with a stack in SQL/PSM. ...
    (microsoft.public.sqlserver.programming)