Postgres 11

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

create table employee(
employee_id int primary key,	
name text,
manager_id int,
department text,
lob text);

insert into employee
(employee_id,name,manager_id,department,lob)
values
(12,'a',null,'IT','BFI'),
(3,'b',12,'sales',null),
(4,'c',3,null,'Banking'),
(6,'d',12,null,null),
(7,'e',4,null,null),
(10,'f',7,null,null);
6 rows affected

select * from employee


employee_id
name
manager_id
department
lob


12
a
null
IT
BFI

3
b
12
sales
null

4
c
3
null
Banking

6
d
12
null
null

7
e
4
null
null

10
f
7
null
null


with RECURSIVE inherit_parent_values as (
select * from employee
where employee_id=3
union
select e.manager_id,p.name,p.manager_id,
coalesce(e.department,p.department),
coalesce(e.lob,p.lob)
from employee p inner join
inherit_parent_values e 
ON e.manager_id=p.employee_id)

select * from inherit_parent_values


employee_id
name
manager_id
department
lob


3
b
12
sales
null

12
a
null
sales
BFI


with RECURSIVE inherit_parent_values as (
select * from employee
where employee_id=10
union
select e.manager_id,p.name,p.manager_id,
coalesce(e.department,p.department),
coalesce(e.lob,p.lob)
from employee p inner join
inherit_parent_values e 
ON e.manager_id=p.employee_id)

select * from inherit_parent_values


employee_id
name
manager_id
department
lob


10
f
7
null
null

7
e
4
null
null

4
c
3
null
Banking

3
b
12
sales
Banking

12
a
null
sales
Banking

employee_id	name	manager_id	department	lob
12	a	null	IT	BFI
3	b	12	sales	null
4	c	3	null	Banking
6	d	12	null	null
7	e	4	null	null
10	f	7	null	null