CODEWITHSUNDEEP
sql-servernestedhierarchy
STFE
STFE

Reputation: 11

SQL: Building hierarchies and nesting queries on the same table

I am trying to build hierarchies by nesting queries on the same table in MS SQL Server 2014. To give an example of what I am trying to achieve:

I have a table 'employees' whith the following columns:

[ID],[First Name],[Last Name],[ReportsTo]

{1},{John},{Doe},{2}

{2},{Mary},{Miller},{NULL}

I am trying to build a statement, where I join the employees table with itself and where I build a hierarchy with the boss on top.

Expected Result:

[Employee],[Boss]

{Miller,Mary},{NULL}

{Doe, John},{Miller,Mary}

I apologize, if this is a stupid question, but I fail to create a working nested query.

Could you please help me with that?

Thank you very much in advance

Upvotes: 1

Views: 785

Answers (1)

David
David

Reputation: 218847

Based on the intended results, it looks like what you essentially want is a list of employees. So let's start with that:

SELECT LastName, FirstName, ReportsTo FROM Employees

This gives you the list, so you now have the objects you're looking for. But you need to fill out more data. You want to follow ReportsTo and show data from the record to which that points as well. This would be done exactly as it would if the foreign key pointed to a different table. (The only difference from being the same table is that you must use table aliases in the query, since you're including the same table twice.)

So let's start by joining the table:

SELECT e.LastName, e.FirstName, e.ReportsTo
FROM Employees e
  LEFT OUTER JOIN Employees b on e.ReportsTo = b.ID

The results should still be the same, but now you have more data to select from. So you can add the new columns to the SELECT clause:

SELECT
  e.LastName AS EmployeeLastName,
  e.FirstName AS EmployeeFirstName,
  b.LastName AS BossLastName,
  b.FirstName AS BossFirstName
FROM Employees e
  LEFT OUTER JOIN Employees b on e.ReportsTo = b.ID

It's a join like any other, it just happens to be a join to the same table.

Upvotes: 1

Related Questions