On my model, I did it like this. I am not sure about its correctness or
minimality or cost-effectiveness.
select t3.lft,t3.rgt from tree t1, tree t2, tree t3 where t1.lft=19 and
t2.lft between t1.lft and t1.rgt and
t3.lft between t2.lft+1 and t2.rgt-1 and t2.lft!=t3.lft group by
t3.lft,t3.rgt having count(*)=1;
This is provided "as-is". ;-)
Condition "t1.lft=19" is to be substituted by your condition to choose the
parent node.
Good luck! If you get other interesting requests/results with that model,
could you please post them to the list or mail them to me?
Antonio Fiol
knut.suebert@web.de wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html