Re: Storing a tree

Christian Meunier schrieb:
> Instead of the adjacency model, you can try the nested sets one.
> Here is the Celko's article on this issue:

Hello,

as that very interesting article was on [SQL] and I got no answer
there to a question, I'm so impolite to send my question here again:

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)

How to limit this result to (Albert's indentation)+1?

Thanks,
Knut Sübert