Problem solve Get help with specific problems with your technologies, process and projects.

Using SQL to access tree branches

I have a table that represents a tree. It has a GUID column and a ParentGUID column. When I am at a specific node (guid) in the tree (in the view), I want to be able to get the whole tree branch below this node by using SQL. Is that possible by only GUID and ParentGUID?

What you've described is a table which follows the very popular Adjacency List Model of Trees, described in Joe Celko's book, SQL for Smarties. I'll get right to it and say that most vendors' SQL implementations have poor support for trees. In order to support trees in SQL, we need some mechanism for performing hierarchical joins. I only know of two vendors which support recursive sets in their SQL implementations, but I'll get to those in a moment.

In the Adjacency List Model, we might have a table for an entity, such as Employee, and a column in that table for its "parent", in this case, the Employee's Manager, which refers to another row of the same table. The table might look like this:

create table Employee
( ID numeric(9) primary key,
  FirstName varchar(20) not null,
  LastName varchar(20) not null,
  Manager numeric(9) references Employee
Notice the column Manager has a foreign key referencing the ID column of its own table, Employee. Sample data might look like this:
insert into Employee values (1, 'Neil', 'Young', NULL);
insert into Employee values (2, 'Don', 'Henley', 1);
insert into Employee values (3, 'Joe', 'Walsh', 1);
insert into Employee values (4, 'David', 'Crosby', 2);
insert into Employee values (5, 'Don', 'Felder', 3);
insert into Employee values (6, 'Stephen', 'Stills', 3);
insert into Employee values (7, 'Timothy', 'Schmit', 2);
insert into Employee values (8, 'Glenn', 'Frey', 1);
insert into Employee values (9, 'Graham', 'Nash', 7);
The data represented in a tree would look something like this:
Neil Young
+-Don Henley
| +-David Crosby
| +-Timothy Schmit
|   +-Graham Nash
+-Joe Walsh
| +-Don Felder
| +-Stephen Stills
+--Glenn Frey
So how do we select all of Don Henley's reports? DB2 supports trees using the WITH operator. Oracle uses a CONNECT BY operator. Unfortunately, users of other databases tend to be forced to use stored procedures, host languages or report writers to traverse trees.

To complete the example, here is the Oracle SQL to answer the question of who reports to Don Henley:

select FirstName || ' ' || LastName EmpName
  from Employee
  connect by prior ID = Manager
  start with Manager = 2;
And here are the results:
David Crosby
Timothy Schmit
Graham Nash
In his book, Joe Celko also discusses a different model for trees called the Nested Set Model. I won't go into details about the model, but I will say it uses an impressively clever method of storing hierarchical data in a way that lends itself well to standard SQL. I recommend chapters 28 and 29 of Joe's book to anyone designing trees.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.