How To Traverse a Tree/Work With Hierarchical data in SQL Code

Question

Say I have an employee table, with a record for each employee in my company, and a column for supervisor (as seen below). I would like to prepare a report, which lists the names and title for each step in a supervision line. eg for dick robbins, 1d #15, i'd like a list of each supervisor in his "chain of command," all the way to the president, big cheese. I'd like to avoid using cursors, but if that's the only way to do this then that's ok.

id  fname   lname   title   supervisorid
1   big     cheese  president   1
2   jim     william vice president  1
3   sally   carr    vice president  1
4   ryan    allan   senior manager  2
5   mike    miller  manager 4
6   bill    bryan   manager 4
7   cathy   maddy   foreman 5
8   sean    johnson senior mechanic 7
9   andrew  koll    senior mechanic 7 
10  sarah   ryans   mechanic    8
11  dana    bond    mechanic    9
12  chris   mcall   technician  10
13  hannah  ryans   technician  10
14  matthew miller  technician  11
15  dick    robbins technician  11

The real data probably won't be more than 10 levels deep...but I'd rather not just do 10 outside joins...I was hoping there was something better than that, and less involved than cursors.

Thanks for any help.

Answer 1

This is basically a port of the accepted answer on my question that I linked to in the OP comments.

you can use common-table expressions

WITH Family As 
( 
    SELECT e.id, e.supervisorid, 0 as Depth
    FROM Employee e
    WHERE id = @SupervisorID 
    UNION All 
    SELECT e2.ID, e2.supervisorid, Depth + 1
    FROM Employee e2
        JOIN Family 
            On Family.id = e2.supervisorid 
) 
SELECT*
FROM Family 

For more:

Recursive Queries Using Common Table Expressions

Answer 2

You might be interested in the "Materialized Path" solution, which does slightly de-normalize the table but can be used on any type of SQL database and prevents you from having to do recursive queries. In fact, it can even be used on no-SQL databases.

You just need to add a column which holds the entire ancestry of the object. For example, the table below includes a column named tree_path:

+----+-----------+----------+----------+
| id | value     | parent   | tree_path|
+----+-----------+----------+----------+
|  1 | Some Text |        0 |          |
|  2 | Some Text |        0 |          |
|  3 | Some Text |        2 |       -2-|
|  4 | Some Text |        2 |       -2-|
|  5 | Some Text |        3 |     -2-3-|
|  6 | Some Text |        3 |     -2-3-|
|  7 | Some Text |        1 |       -1-|
+----+-----------+----------+----------+

Selecting all the descendants of the record with id=2 looks like this:

SELECT * FROM comment_table WHERE tree_path LIKE '-2-%' ORDER BY tree_path ASC

To build a tree, you can sort by tree_path to get an array that's fairly easy to convert to a tree.

You can also index tree_path and the index can be used when the wildcard is not at the beginning.

For example, tree_path LIKE '-2-%' can use the index, but tree_path LIKE%-2-'` cannot.