SELECT grand parent of a row with in a single table - SQL

Question

I have a table with following hypothetical data:

ID | Name    | managerId
---|---------|-----------
1  | James   | 2
2  | Martin  | 4
3  | Huges   | 5
4  | Richard | NULL
5  | Kavin   | 4
6  | Rita    | 3

In the above table we have:

Richard is manager of Martin is manager of James.

Richard is manager Kavin is manager of Huges is manager of Rita.

Now I have to select immediate manager and grand manager of an employee in this table. I mean if we start from Rita I have to select Huges (immediate manager) and Richard(grand manager).

How to do that. I have no clue at all.

EDIT:
There isn't any specific levels of managers. An Employee can have 'n' Number for managers in hierarchy where n ϵ {0,1,2,3.....}

I voted to re-open, because I disagree with the solutions for only two levels. — Gordon Linoff, Oct 04 '15 at 12:31

score 0 · Answer 1 · edited May 23 '17 at 12:22

Because you only need two levels, this is quite feasible to do this with a self join, twice. Also, you probably want left join:

select t.*, tm.name as managername, tmm.name as manager_managername
from t left join
     tm
     on t.managerId = tm.id left join
     tmm
     on tm.managerId = tmm.id;

You can add where t.name = 'Rita'.

If you have to do this up to any level, then you should definitely review this question. The short answer is that you probably need another data structure to handle this type of query in MySQL.

Kostas Mitsarakis · Answer 2 · 2015-10-04T17:50:14.793

EDIT:

The following answer works only for a specific level of parents (as question was asking before the change). I am sure that for N levels it needs a completely different approach.

You can use this. It's going up to three levels of managers and finds all employees who have at least 2 levels of managers.

SELECT aa.Name AS employee, bb.Name AS parent_manager, cc.Name AS grandparent_manager, dd.Name AS grand_grandparent_manager
FROM t1 AS aa
INNER JOIN t1 AS bb
ON aa.managerId = bb.ID
INNER JOIN t1 AS cc
ON bb.managerId = cc.ID
LEFT JOIN t1 AS dd
ON cc.managerId = dd.ID

And this is if you want the managers for a specific employee.

SELECT aa.Name AS employee, bb.Name AS parent_manager, cc.Name AS grandparent_manager, dd.Name AS grand_grandparent_manager
FROM t1 AS aa
INNER JOIN t1 AS bb
ON aa.managerId = bb.ID
INNER JOIN t1 AS cc
ON bb.managerId = cc.ID
LEFT JOIN t1 AS dd
ON cc.managerId = dd.ID
WHERE aa.Name = 'Rita'

There isn't any specific levels of managers. An Employee can have 'n' Number for managers where n ϵ {0,1,2,3.....} — Tariq, Oct 04 '15 at 17:40

SELECT grand parent of a row with in a single table - SQL

2 Answers2