Building heirarchal answerset from single table

Database
Enthusiast

Building heirarchal answerset from single table

I have a table that is a list of users that I need get as a heirachal answer set. This table has people from 1st level, 2nd level, and 3rd level in the organizations heirarchy all built the same way..i.e.

user_id first_name last_name supervisor_id

My problem is that supervisors are built the same way, where the supervisor_id is actually a 2nd line manager. What I need to do is retrieve users by specifying the 3rd level (second line manager) , and list it as such:

user_id first_name last_name supervisor_id second_line_manager

Whit the supervisors being built the same as a user, I need to devise a way to make an answer set that lists each user, their boss, and their bosses boss. Is this a problem that a recursive query could handle? My appologies if this is a rudamentary question, I'm still learning as I go.
1 REPLY
Enthusiast

Re: Building heirarchal answerset from single table

You do not need a recursive query for this - just use a Join.

Create Table OrgHier
(User_Id Smallint Not Null
, First_Name Varchar(20) Not Null
, Last_Name Varchar(30) Not Null
, Supervisor_Id Smallint)
Unique Primary Index(User_Id)
;

Insert Into OrgHier Values (1, 'Barack', 'Obama',Null);
Insert Into Orghier Values(21, 'General' , 'Army', 1);
Insert Into OrgHier Values (22, 'General', 'USAF', 1);
Insert Into OrgHier Values (23, 'Admiral','USNavy', 1);

Insert Into Orghier Values(211, 'Captain' , 'Army', 21);
Insert Into OrgHier Values (2111, 'Leutenant' ,'Army', 211);
Insert Into OrgHier Values (21111, 'PotatoPeeler','Army', 2111);

Select * From OrgHier
Order By 1
;

Select
A.User_Id
, A.First_Name
, A.Last_Name
, A.Supervisor_Id As Boss_Id
, B.Supervisor_Id As Bosses_Boss_Id
From OrgHier A
Left Join OrgHier B
On A.Supervisor_Id = B.User_Id
Order By 1
;