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.

You might be interested in my question: stackoverflow.com/questions/3471134/… — Neil N
– Neil N, Commented Jul 11, 2011 at 18:33
Your data has a little problem, record id 1 cannot have supervisorid=1 it will break the CTE table — AaA
– AaA, Commented Dec 17, 2014 at 9:17

Simone · Accepted Answer · 2019-07-31 14:36:57Z

23

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

edited Jul 31, 2019 at 14:36

Simone

11.8k1 gold badge33 silver badges45 bronze badges

answered Jul 11, 2011 at 18:37

Neil N

25.3k17 gold badges87 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Albert Over a year ago

Whats the @ character for exactly? Do you have to provide a parameter? I wasn't clear, but what I ultimately want to do is provide a list of all employees, and all their respective "chains of command"...for each employee, not just one. Can you alter your query to perform this?

Neil N Over a year ago

The @SupervisorID is if you want to have a "root" person to a chain. If you want all chains, just drop the where clause line altogether and it should work.

user4843530 Over a year ago

I think the @ SupervisorID is the key to the recursion. The second query inside the CTE (the recursive member) calls back to Family with an employee id. The first query (anchor member) has to be able to only grab the employees that are under that given employee id on each recursion. Since the first time you access the anchor member, you are not providing an employee id, you start with the top level, i.e. employees with a null supervisor id. If you did not have that @ SupervisorID, you'd break the recursion.

Ali Gangji · Accepted Answer · 2022-07-26 19:38:33Z

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.

Sunny Milenov · Accepted Answer · 2011-07-11 18:38:49Z

0

Some recursive function which either return the supervisor (if any) or null. Could be a SP which invokes itself as well, and using UNION.

answered Jul 11, 2011 at 18:38

Sunny Milenov

22.4k7 gold badges84 silver badges107 bronze badges

Comments

S Chapman · Accepted Answer · 2018-10-23 09:50:32Z

0

SQL is a language for performing set operations and recursion is not one of them. Further, many database systems have limitations on recursion using stored procedures as a safety measure to prevent rogue code from running away with precious server resources.

So, when working with SQL always think 'flat', not 'hierarchical'. So I would highly recommend the 'tree_path' method that has been suggested. I have used the same approach and it works wonderfully and crucially, very robustly.

answered Oct 23, 2018 at 9:50

S Chapman

1

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related