Re: Recursive select

--CELKO-- schrieb:

> Another way of representing trees is to show them as nested sets. 

Good evening, 

that's what I needed!

To limit the result to entries below one node, I'd use something like

  SELECT P1.lft, COUNT(P2.emp) AS indentation, P1.emp 
    FROM Personnel AS P1, Personnel AS P2 
    WHERE P1.lft BETWEEN P2.lft AND P2.rgt
      AND p1.lft>(SELECT lft FROM personnel WHERE emp='Chuck')
      AND p1.rgt<(SELECT rgt FROM personnel WHERE emp='Chuck')
    GROUP BY P1.emp, p1.lft ORDER BY P1.lft;

   lft | indentation |    emp     
  -----+-------------+------------
     5 |           3 | Donna     
     7 |           3 | Eddie     
     9 |           3 | Fred      
  (3 rows)

for emp='Albert' it returns

   lft | indentation |    emp     
  -----+-------------+------------
     2 |           2 | Bert      
     4 |           2 | Chuck     
     5 |           3 | Donna     
     7 |           3 | Eddie     
     9 |           3 | Fred      
  (5 rows)

My question is, how to limit this result to (Albert's indentation)+1?

Thanks,
Knut Sübert