Joe Celko's SQL for Smarties: Advanced SQL Programming Second Edition (The Morgan Kaufmann Series in Data Management Systems)

You find some clever methods for tree handling in the book of Joe Celko:

http://www.amazon.com/Joe-Celkos-SQL-Smarties-Programming/dp/1558605762

...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:

• ID of the node you are working on
• Highest visitation number used so far (the next LEFT is this + 1)

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

• Download one of the free SQL databases, for example SQL Server Express or MySQL.
• Learn the basics of relational databases.
• Learn about database normalization.
• Buy Alan Beaulieu's book Learning SQL.
• Buy Kevin Kline's book SQL In A Nutshell.
• For more advanced stuff, buy Celko's book SQL for Smarties.
• Read everything written by Erland Sommarskog.
• Play around with SQLzoo.
• Using examples from the above resources, start practicing.
• Write a personal application that is SQL-centric - this will give you a specific context for understanding the ideas and examples.

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!)