MySQL recursive subquery

Question

Not sure the title accurately reflects what I need to do and this question is possibly out of the scope of Stack Overflow as I do not have much to work with, other than my problem. I will explain my scenario ...

I have a reporting web application. There are three levels of users Senior Managers, Managers and Team Members. Each Manager is responsible for a team. Each Senior Manager is responsible for a subset of Managers.

Senior Managers and Managers write a report on their subordinates. Currently the system only allows Manager to view reports on their immediate subordinates. However the system is to be adapted to allow Senior Managers to view reports on their Managers as well as view any reports the Managers have written about their team members.

I have table team_members table which governs which team a user is in. So ...

Users
--------
uid
forename
surname

team_members
------------
id
leader_id
member_id

leader_id and member_id relate to the primary key of the users table; uid.

So as a Senior Manager I need to be able to generate a list of all immediate team members (Managers), plus a list of their subordinates. Is there a way to do this within one query?

So you need to pull a hierarchical set of data from the database? — Orbling, Oct 01 '13 at 15:40
Related: http://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes — Orbling, Oct 01 '13 at 15:42
How is the relationship between the Senior manager and the manager defined? — Tom, Oct 01 '13 at 16:45

score 0 · Accepted Answer · answered Oct 01 '13 at 17:25

i'm not sure if herachial data is what you need/want, but here's what you need

all tops, middle, an regular members, DEMO, SQL :

select 
IFNULL((SELECT name FROM users where uid=smng.leader_id),(SELECT name FROM users where uid=mng.leader_id)) 
         as top, 
(SELECT name FROM users where uid = mng.leader_id) 
         as middle,
usr.name as regular
from 
 users usr inner join managers mng on usr.uid=mng.member_id 
    inner join managers smng on mng.leader_id = smng.member_id

for specified top manager add :

where smng.leader_id =1

for a specified middle manager change to :

where mng.leader_id =2

MySQL recursive subquery

1 Answers1

Linked