Sorry for my bad english.
I want to add a child(Tom) to Chuck but not on the left of DONNA but
on the right of FRED.
Can You help me ?



LOCK TABLE main_menu WRITE;
SELECT @myLeft := lft FROM main_menu
WHERE category_id = "Chuck";
UPDATE main_menu SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE main_menu SET lft = lft + 2 WHERE lft > @myLeft;
INSERT INTO main_menu(name_ita,name_eng, lft, rgt) VALUES('Tom','Tom',
@myLeft + 1, @myLeft + 2);
UNLOCK TABLES;



Albert (1, 12)
/ \
/ \
Bert (2, 3) Chuck (4, 11)
/ | \
/ | \
/ | \
/ | \
Donna (5, 6) Eddie (7, 8) Fred (9, 10)

oal (AT) tiscali (DOT) it wrote:
I waited to see if Joe would respond. He must be busy. If you searched
the group archive, you could find Joe's description (or maybe you have
his book?)

http://groups.google.com/group/comp....ebcebc638f53bb

That article describes the insert this way:
<quote Joe Celko>
5. The nested set model has an implied ordering of siblings which the
adjacency list model does not. To insert a new node as the rightmost
sibling.


BEGIN
DECLARE right_most_sibling INTEGER;


SET right_most_sibling
= (SELECT rgt
FROM Personnel
WHERE emp = :your_boss);


UPDATE Personnel
SET lft = CASE WHEN lft > right_most_sibling
THEN lft + 2
ELSE lft END,
rgt = CASE WHEN rgt >= right_most_sibling
THEN rgt + 2
ELSE rgt END
WHERE rgt >= right_most_sibling;


INSERT INTO Personnel (emp, lft, rgt)
VALUES ('New Guy', right_most_sibling, (right_most_sibling + 1))
END;
<quote end>

So it looks like you go astray on the first select when you get the
left instead of the right.

HTH,
Ed

I did not check it, but Ed gave you the answer. The prinicple is that
you first make a gap in the tree's numbers by increasing all the lft
and rgt numbers that are to the right of the insertion point. The new
subtree then goes in that gap. Chapter 5 in TREES & HIERARCHIES gives
other approaches to insertion.