Re: Storing a tree

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