Re: yet another hierarchy model

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 28 Oct 2001 13:05:12 -0800
Message-ID: <c0d87ec0.0110281305.1066a714_at_posting.google.com>

I got a little time to play with this model and my nested sets. I have a few versions of views for the Immediate_subordinates(boss, worker, lft, rgt), since it seems to be a frequent query. I can get the level and the sibling position in a nested set table with this.

 SELECT T2.emp, COUNT(T1.lft) AS level
   FROM Personnel AS T1, Personnel AS T2   WHERE T2.lft BETWEEN T1.lft AND T1.rgt  GROUP BY T2.emp;

I think this query can be made better.

SELECT P1.emp AS boss, S1.worker,

       COUNT(S2.lft) AS sibling_order
  FROM Immediate_subordinates AS S1,

       Immediate_subordinates AS S2,
       Personnel AS P1
 WHERE S1.boss = P1.emp 

   AND S2.boss = S1.boss
   AND S2.lft <= S1.lft
 GROUP BY S1.worker; Received on Sun Oct 28 2001 - 22:05:12 CET