Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table organizations (
 org_id serial primary key, 
 org_name varchar(100) not null
);
insert into organizations (org_name) values
('org one'), ('org two'), ('org 3'), ('org 4');
4 rows affected

create table employees (
 emp_id serial primary key, 
 emp_name varchar(100) not null, 
 emp_title varchar(30) not null
);
insert into employees (emp_name, emp_title) values
  ('Razor', 'CEO')
, ('Michael', 'Manager')
, ('Romus', 'Tamer')
, ('Lion', 'Pet')
;
4 rows affected

create table employee_relations (
 emp_id int not null, 
 org_id int not null, 
 path varchar(100) not null, 
 primary key (emp_id, org_id), 
 foreign key (emp_id) references employees(emp_id), 
 foreign key (org_id) references organizations(org_id) 
)
insert into employee_relations (org_id, path, emp_id) values
(1, '1',        1), 
(1, '1.2',      2), 
(2, '1.2',      2), 
(1, '1.2.3',    3), 
(2, '1.2.3',    3), 
(3, '1.2.3',    3), 
(1, '1.2.3.4',  4), 
(2, '1.2.3.4',  4), 
(3, '1.2.3.4',  4), 
(4, '1.2.3.4',  4);
10 rows affected

with recursive rcte as (
    select base_emp_id, base_org_id, emp_id, org_id, path 
    , 1 as lvl
    from cte
    
    union all
    
    select 
      c.base_emp_id, c.base_org_id
    , t.emp_id, t.org_id, t.path
    , c.lvl+1
    from rcte c
    join employee_relations t
      on c.path = concat(t.path,'.',c.emp_id)
)
, cte as (
    select 
    r1.emp_id as base_emp_id, 
    r1.org_id as base_org_id, 
    r2.emp_id, 
    r2.org_id, 
    r2.path
    from employee_relations r1
    left join employee_relations r2
      on r2.org_id = r1.org_id
     and r1.path = concat(r2.path,'.',r1.emp_id)
), cte2 as (
select 
 c1.base_emp_id as emp1, 
 c1.base_org_id as org1, 
 c1.emp_id as emp2, 
 c1.base_org_id as org2, 
 c2.emp_id as emp3, 
 max(c2.org_id) as org3
from cte c1
left join rcte c2 
  on c2.base_emp_id = c1.base_emp_id
 and c2.base_org_id = c1.base_org_id
 and c2.lvl > 1
group by c1.base_emp_id, c1.base_org_id, c1.emp_id, c2.emp_id
) 
select 
  coalesce(e3.emp_name, e2.emp_name, e1.emp_name) as bases
, coalesce(c.org3, c.org1) as part_id
, coalesce(e2.emp_name, e1.emp_name) as part_of
, c.org1 as viewer_org_id
, e1.emp_name as name   
, e1.emp_id as id
from cte2 c
left join employees e1 on e1.emp_id = c.emp1
left join employees e2 on e2.emp_id = c.emp2
left join employees e3 on e3.emp_id = c.emp3
order by emp1, org1, emp2, org3, emp3


bases
part_id
part_of
viewer_org_id
name
id


Razor
1
Razor
1
Razor
1

Razor
1
Razor
1
Michael
2

Michael
2
Michael
2
Michael
2

Razor
1
Michael
1
Romus
3

Razor
1
Michael
2
Romus
3

Romus
3
Romus
3
Romus
3

Razor
1
Romus
1
Lion
4

Michael
2
Romus
1
Lion
4

Razor
1
Romus
2
Lion
4

Michael
2
Romus
2
Lion
4

Razor
1
Romus
3
Lion
4

Michael
2
Romus
3
Lion
4

Lion
4
Lion
4
Lion
4

bases	part_id	part_of	viewer_org_id	name	id
Razor	1	Razor	1	Razor	1
Razor	1	Razor	1	Michael	2
Michael	2	Michael	2	Michael	2
Razor	1	Michael	1	Romus	3
Razor	1	Michael	2	Romus	3
Romus	3	Romus	3	Romus	3
Razor	1	Romus	1	Lion	4
Michael	2	Romus	1	Lion	4
Razor	1	Romus	2	Lion	4
Michael	2	Romus	2	Lion	4
Razor	1	Romus	3	Lion	4
Michael	2	Romus	3	Lion	4
Lion	4	Lion	4	Lion	4