I’m well aware that adjacency tables aren’t the newest way of storing hierarchies in a database. Personally I prefer to use nested sets. But on this one project I had to work with a legacy database. It took me quite a while to find out how to retrieve an entire tree in one requests. This is obviously needed to speed up queries on large amounts of data.
This query is here to help me remember how to do it.
DROP TABLE People;
CREATE TABLE People (
id INT NOT NULL PRIMARY KEY IDENTITY
, boss_id INT
, name VARCHAR(50)
);
SET IDENTITY_INSERT People ON
INSERT INTO People (id, boss_id, name)
SELECT 1, NULL, 'Bert'
UNION ALL SELECT 2, 1, 'Sylvie'
UNION ALL SELECT 3, 1, 'Sylvia'
UNION ALL SELECT 4, 2, 'Davy'
UNION ALL SELECT 5, 2, 'Xavier'
UNION ALL SELECT 6, 3, 'Kristof'
UNION ALL SELECT 7, 3, 'Piet';
WITH Bosses (id, boss_id, name, depth, hierarchy)
AS (
SELECT id
, boss_id
, name
, 0
, name
FROM People
WHERE boss_id IS NULL
UNION ALL
SELECT People.id
, People.boss_id
, People.name
, Bosses.depth + 1
, CONVERT(VARCHAR(50), Bosses.hierarchy + ' > ' + People.name)
FROM People
, Bosses
WHERE People.boss_id = Bosses.id
)
SELECT *
FROM Bosses
ORDER BY Bosses.hierarchy;
| id | boss_id | name | depth | hierarchy |
|---|---|---|---|---|
| 1 | NULL | Bert | 0 | Bert |
| 3 | 1 | Sylvia | 1 | Bert > Sylvia |
| 6 | 3 | Kristof | 2 | Bert > Sylvia > Kristof |
| 7 | 3 | Piet | 2 | Bert > Sylvia > Piet |
| 2 | 1 | Sylvie | 1 | Bert > Sylvie |
| 4 | 2 | Davy | 2 | Bert > Sylvie > Davy |
| 5 | 2 | Xavier | 2 | Bert > Sylvie > Xavier |