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 MgrFROM Hr as emp, ReportsTo as r, Hr as emp2WHERE 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.