postgresql employee inherits manager data for missing values-CodePudding

I'm trying to write SQL to fill the missing values for an employee. A manager is also an employee. Values missing for an employee can be inherited from manager, if values are not there for an employee's manager, then will lookup to manager's manger ( have to look upto root manager if required, till we get values) values.

Below is table and data

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);

sample data is

Similarly have to inherit values for lob column to replace nulls for each employee.

Example: neither employee 10 nor his manager 7 doesn't have department and lob value, but employee/manger 7 has manager 4 and manager 4 has lob value Banking and department value sales inherit from his manager. So employee 10 and employee 7 will inherit department and lob values Sales and Banking. Expected result for employee 10 is

my query

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

Trying using recursive query but this is giving all managers rows upto root level of each employee instead of one row for each employee.

Also have to get not null values in column level. seems coalesce is not useful in my query

Is it possible in PostgreSQL to fill employee null values from managers ?

Expected result is

CodePudding user response：

demo: db<>fiddle

You did it bottom-up (starting with the leaf - an employee), I'd do it top-down (starting at the root - a top-manager):

WITH RECURSIVE rec AS (
    SELECT *
    FROM employee
    WHERE manager_id IS NULL
    
    UNION
    
    SELECT 
        e.employee_id,
        e.name,
        e.manager_id,
        COALESCE(e.department, r.department) as department,
        COALESCE(e.lob, r.lob) as lob
    FROM employee e
    JOIN rec r ON r.employee_id = e.manager_id
)
SELECT * FROM rec