Press "Enter" to skip to content

SQL Graph and Hierarchies

Hasan Savran shows how you can use SQL Server graph tables to handle hierarchical data:

The > determines the direction of the query. By changing this arrow into other direction, you can use the same relation table to find who manages employee. For example, let’s rewrite this query and look who manages East Manager

SELECT emp.Name as Emp, emp2.Name as Mgr
FROM Hr as emp, ReportsTo as r, Hr as emp2 
WHERE MATCH(emp<-(r)-emp2) and emp.empid = 8

There are a few relational approaches which work well for hierarchies, though they all come with tradeoffs in terms of read complexity, write complexity, and development complexity. Click through for Hasan’s take on the graph-based approach.