# Finding the first parent (manager) of an employee that makes twice as much in a self-referencing table?

Example you have the following table

``````CREATE TABLE emp (id,name,salary,manager)
AS
VALUES
( 1  , 'james' ,  10000 , null ),
( 2  , 'alex'  ,   5000 , 1 ),
( 3  , 'Alice' ,   4500 , 1 ),
( 4  , 'Jone'  ,   3000 , 3 ),
( 5  , 'Omar'  ,   2200 , 2 );
``````

Anyone who is a manager of your manager is also your indirect manager so Omar has Alex and James as indirect managers. I need a query that will get me the first indirect manager who has twice salary or more than the employee.

So result should be:

``````ID | Manager
1  | null
2  | 1
3  | 1
4  | 1
5  | 2
``````
• How many levels can exists? – McNets Jun 8 '17 at 14:04
• Why do you say "Indirect Manager" when your desired result has 5|2, emp.id 5's direct manager is 2. Also, why is the question "greatest salary" but the body is "first indirect manager who has twice salary" – Evan Carroll Jun 8 '17 at 16:06
• ID=1 should not be there it doesn't match both conditions. – McNets Jun 8 '17 at 18:12

This is a query you can try. Sure it can be simplified but is a good start I think.
The key is to traverse all the managers chain recursively retaining salary information in order to compare later.

``````with recursive t1 as ( -- Traverse emps all the way up in the manager chain
select id, name, salary, manager, salary indsalary, manager indirect, 1 as level
from emp
union all
select t1.id, t1.name, t1.salary, e.manager, e.salary, t1.manager, t1.level + 1
from emp e, t1
where t1.manager = e.id
)
, t2 as ( -- Check salary condition
select *
from t1
where  indsalary >= 2 * salary
)
, t3 as ( -- get the row numbers
select t2.*, row_number() over (partition by id order by level) rn
from t2
)
, t4 as ( -- retain only first row per emp
select * from t3
where rn = 1
)
-- Add emps that has no manager or don't fullfill the salary condition
select e.id, t4.indirect manager
from emp e
left join t4
on (e.id = t4.id);
``````

Result:

`````` id | manager
----+---------
1 |  [null]
2 |       1
3 |       1
4 |       1
5 |       2
(5 rows)
``````
• I hope you stick around and learn a bit more. This is wayy complex and hard to follow. T3/T4 should be distinct on. The salary condition should be folded into the recursive query. dba.stackexchange.com/a/175801/2639 – Evan Carroll Jun 8 '17 at 19:10

Using a recursive function and LATERAL join

Next function returns last indirect manager given a employer id.

``````CREATE OR REPLACE FUNCTION fnManager(pid int)
RETURNS SETOF employees AS
\$BD\$
DECLARE
emp_id int;
manager_id int;
BEGIN
SELECT id, manager FROM employees WHERE id = \$1
INTO emp_id, manager_id;

WHILE manager_id IS NOT NULL LOOP
SELECT id, manager FROM employees WHERE id = manager_id
INTO emp_id, manager_id;
END LOOP;

RETURN QUERY SELECT * FROM employees WHERE id = emp_id;
RETURN;
END
\$BD\$
LANGUAGE 'plpgsql';
``````

Then you can use this function with a LATERAL join to get last indirect manager of each employee, filtering by salary.

``````select emp.id, emp.name, emp.salary, x.id as manager_id , x.name as manager_name, x.salary as manager_salary
from employees emp
join lateral fnManager(emp.id) x ON x.salary >= (emp.salary * 2)
;
``````
```id | name  | salary | manager_id | manager_name | manager_salary
-: | :---- | -----: | ---------: | :----------- | -------------:
2 | Alex  |   5000 |          1 | James        |          10000
3 | Alice |   4500 |          1 | James        |          10000
4 | John  |   3000 |          1 | James        |          10000
5 | Omar  |   2200 |          1 | James        |          10000
```

dbfiddle here

I did something similar for SQL Server. You can take guidelines from this snippet(it has parameterized name) :

``````create table #emp(
id int,
Name varchar(50),
Salary int,
ReportsTo int
);

insert into  #emp
values
(1,'John',10000,null),
(2,'Annie',5000,1),
(3,'Bob',4500,1),
(4,'Dong',3000,3),
(5,'Omar',2200,2)

--select * from #emp;

with rec as (
select #emp.ReportsTo, #emp.id, #emp.Name, #emp.Salary, 1 as level from #emp where Name = 'Omar'
union all
select  #emp.ReportsTo, #emp.id, #emp.Name, #emp.Salary, level + 1 as level from #emp
inner join rec
on #emp.id = rec.ReportsTo
)
select  id, ReportsTo, Name as 'Manager Name',Salary, level from rec
where level = (select min(level) from rec
where salary not in (select distinct top 1 salary
from rec order by salary)
)
OPTION (MAXRECURSION 0)
``````
• I think you can make this a bit easier but not using a subselect min for level. – Evan Carroll Jun 8 '17 at 15:09

# PostgreSQL Recursive Table w/ Running Total

I think something like this is the fastest solution to get what you want.

``````WITH RECURSIVE t(id,salary,manid,runningsalary,tree) AS (
SELECT id, salary, manager, 0, ARRAY[id]::int[]
FROM emp
UNION ALL
SELECT t.id, t.salary, emp.manager, t.runningsalary + emp.salary, emp.id||tree
FROM t
INNER JOIN emp
ON t.manid = emp.id
AND t.runningsalary < 2*t.salary
)
SELECT DISTINCT ON (id)
id,
CASE WHEN runningsalary > salary THEN tree[1] END AS manager
FROM t
ORDER BY id, tree;
id | manager
----+------
1 |
2 |    1
3 |    1
4 |    1
5 |    2
(5 rows)
``````

This generates a recursive table underneath like this,

``````SELECT *
FROM t
ORDER BY id, tree;
id | salary | manid | runningsalary |  tree
----+--------+-------+---------------+---------
1 |  10000 |       |             0 | {1}
2 |   5000 |       |         10000 | {1,2}
2 |   5000 |     1 |             0 | {2}
3 |   4500 |       |         10000 | {1,3}
3 |   4500 |     1 |             0 | {3}
4 |   3000 |       |         14500 | {1,3,4}
4 |   3000 |     1 |          4500 | {3,4}
4 |   3000 |     3 |             0 | {4}
5 |   2200 |     1 |          5000 | {2,5}
5 |   2200 |     2 |             0 | {5}
(10 rows)
``````

`manid` is used for the recursion here, you can ignore it for your purposes. We're building a tree recursing with two edge conditions:

1. When that `runningsalary` exceeds twice the `salary` we stop recursing. That's our edge condition: `t.runningsalary < 2*t.salary`
2. When we no longer have a `manid` (manager's id) we stop recursing (nature of `INNER JOIN`)

With the tabled ordered like this, we see if the `runningsalary` is 2x the salary, if it is we take the first id in the tree (the id to trigger the edge condition). If it's not we display null.

Then we use `DISTINCT ON` which is a PostgreSQL extension to grab only one row per distinct column. The one with the longest tree. We don't care about the intermediary managers that make less. We only care about the last one, or the one that triggered the edge condition `t.runningsalary < 2*t.salary`.

As a side note, you can replace the tree with a smallint `level` or the like and add another column for the current `empid` but I prefer the added verbosity of the array-representation of recursion. Especially for demonstration purposes.