1

I have a emp table as follows:

userID varchar(10)
ManagerID varchar(10)

Data:

UserID    ManagerID
101         102
103         102
104         103

I need to find all the users under a manager directly or indirectly.

The output should be as follows:

If it is for 102:

Manager     ReportingUsers
--------------------------
102           101,103

And if we check for 104 the output should be:

Manager     ReportingUsers
--------------------------
104         102,101,103 `

The query I am writing for achieving this is as follows

SELECT managerID, GROUP_CONCAT(userID) FROM emp GROUP BY managerID;

But the output I get satisfies the first output but not the second:

+-----------+----------------------+
| MANAGERID | GROUP_CONCAT(USERID) |
+-----------+----------------------+
| 102       | 101,103              |
| 104       | 102                  |
+-----------+----------------------+

How do I achieve this use case using Cursors and Stored Procedures through which I can get the output for every manager?

Just an update If I use the below query I get the result for my second example as well but the first example for 102 does not get satisfied:

SELECT m.MANAGERID,  GROUP_CONCAT(m.USERID,';',(Select Group_Concat(n.USERID) from emp as n where m.userID=n.managerID or n.managerID=NULL group by n.managerID)) as ReportingUsers FROM emp as m GROUP BY managerID;

If I can get a solution using cursors and procedures that would also work.

Chirayu Chhibar
  • 11
  • 3
  • 2
    `GROUP_CONCAT` is not a `sql-server` function. Did you mean `MySQL`? Can you please update your question using the right tags? – B3S Sep 27 '19 at 11:23
  • Apologies. I am new to SQL. I would be grateful if you can give me a sql-server query also for this. – Chirayu Chhibar Sep 27 '19 at 11:46
  • Maybe this thread can help you [Mysql-hierarchical-recursive-query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query). – Shaheryar.Akram Sep 27 '19 at 12:19

2 Answers2

0

Your query is correct, it gives the expected output:

mysql> select * from testdb.hierarchy;
+--------+-----------+
| userID | managerID |
+--------+-----------+
| 101    | 102       |
| 103    | 102       |
| 104    | 103       |
+--------+-----------+
3 rows in set (0.00 sec)

mysql> select managerID, group_concat(userID) from hierarchy group by managerID ;
+-----------+----------------------+
| managerID | group_concat(userID) |
+-----------+----------------------+
| 102       | 101,103              |
| 103       | 104                  |
+-----------+----------------------+
2 rows in set (0.00 sec)
B3S
  • 1,021
  • 7
  • 18
  • This only satisfies my first use case and not the second one. under 103 I want to show 104 along with 101 and 103 as they have indirect reporting as well. – Chirayu Chhibar Sep 27 '19 at 11:44
  • sorry, my bad, i missed your "indirect" connection between user and a "2nd level" manager, i'll edit my answer soon – B3S Sep 27 '19 at 11:54
0

You might consider switching to a nested set model:

user_id lft rgt
    102   1   8
    101   2   3
    103   4   7
    104   5   6

Otherwise, confirm which database/version you're using.

Strawberry
  • 33,750
  • 13
  • 40
  • 57