I have a table that represents a tree. It has a GUID column and a ParentGUID column. When I am at a specific node...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
(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 );
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);
Neil Young +-Don Henley | +-David Crosby | +-Timothy Schmit | +-Graham Nash +-Joe Walsh | +-Don Felder | +-Stephen Stills +--Glenn Frey
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;
EmpName -------------- David Crosby Timothy Schmit Graham Nash
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.