You find some clever methods for tree handling in the book of Joe Celko:
...however, I don't know if it covers your problem
Calculating left and right visitation numbers is an iterative, procedural operation. It can't be done in a single query, but you can do it with a single procedure that calls itself recursively.
Your procedure needs to have two parameters:
The procedure needs to return the highest visitation number it has applied (the last RIGHT).
Within the procedure, you run a query to find all of the children of the node with the given ID. If they are in order, you can sort accordingly - or you can use an arbitrary order. For each child found, set the left number and then call the procedure recursively on that child. When the recursive call returns, use the return code to set the right visitation number.
Joe Celko has a whole chapter in a book dedicated to how to be clever and efficient with visitation number calculations. If your data set is really big and is edited frequently, you might want to read up on that - otherwise you can just recalculate all of your visitation numbers with every edit to your node list.
SQL for Smarties by Joe Celko. I like the way it covers methods for representing datasets that you'd not immediately think would fit into a relational model.
alt text http://images.amazon.com/images/P/0123693799.01._SX140_SY225_SCLZZZZZZZ_.jpg
Check out SQL For Smarties. I thought I was pretty good with SQL too, until I read that book... Goes into tons of depth, talks about things I've not seen elsewhere (I.E. difference between 3'rd and 4'th normal form, Boyce Codd Normal Form, etc)...
The seminal resource for this are chapters 28-30 of SQL for Smarties.
(I've recommended this book so much I figure Celko owes me royalties by now!)