Question 226455 in Mysql Question posted in
The official MySQL documentation can be found here.

how to get all members under me in mysql SQL Stored Procedures

In MySQL SQL Stored Procedures, I need to retrieve all referrals under me at all levels while using where under referrals gives one level only

Sample Data

Name Referred By
Alice
Bob Alice
Carol Alice
Dave Bob
Eve Bob
Frank Carol
Grace Eve
Helen
David Helen
Lucas David
Olivia Lucas
Sophia Lucas
Benjamin Bob

Expected Result
I asked for ‘Alice’ all level referrals then i need like this

  • Bob
  • Carol
  • Dave
  • Eve
  • Frank
  • Grace
  • Benjamin

where these shouldn’t display (David,Lucas,Olivia ,Sophia) is no way related to Alice

Tags:

2

Answers


  1. 0 
    WITH RECURSIVE
cte AS (
    SELECT *
    FROM table
    WHERE name = ?
    -- or, if initial person shouldn't be returned,
    -- WHERE referred_by = ?
  UNION ALL
    SELECT table.*
    FROM table
    JOIN cte ON cte.name = table.referred_by
)
SELECT name
FROM cte
    Login or Signup to reply.
  2. 0

    Using a recursive cte is really convenient and should be strongly recommended. However, the job can be accomplished without one by using a stored procedure, which requires temporary tables. Only do it when recursive CTE is out of reach. i.e mysql version 5.x and below.

    select * from the test;
+----------+-------------+
| Name     | Referred_by |
+----------+-------------+
| Alice    |             |
| Bob      | Alice       |
| Carol    | Alice       |
| Dave     | Bob         |
| Eve      | Bob         |
| Frank    | Carol       |
| Grace    | Eve         |
| Helen    |             |
| David    | Helen       |
| Lucas    | David       |
| Olivia   | Lucas       |
| Sophia   | Lucas       |
| Benjamin | Bob         |
+----------+-------------+

delimiter //
drop procedure if exists test_proc //
create procedure test_proc(leader varchar(10))
begin
drop temporary table if exists tmp; 
drop temporary table if exists result;
create temporary table tmp (name varchar(10)); -- tmp stores names of leaders (aka referred_by in this case) for one recursive cycle
create temporary table result(name varchar(10), is_new bool default 1); -- result stores names of people referred by the leaders. The newly added names are also appointed as new leaders and to be moved to the tmp table.

insert result (name) select name from test where referred_by=leader; -- the very same names referred by the original leader are also to be recorded here
insert tmp select name from result where is_new=1; -- Get the names which are referred by the original leader.  The IN argument from the procedure is passed here. Alice is going to be used as the original leader in the test. Note, the WHERE clause in the query is superfluous for the first recursive round.

UPDATE result SET is_new = 0; -- once the names are moved to tmp , they should be set to the status as used (no longer qualifed as leader candidates)

while exists (select 1 from tmp) do -- as long as there is one instance(leader) in tmp, the recursive cycles go on 

insert result (name) 
select name from test where referred_by in (select name from tmp) -- get the names referred by the leaders into the result table
;


DELETE FROM tmp; -- clean up the current leaders' list
insert tmp (select `name` from result where is_new=1); -- get the NEW leaders from the result table
UPDATE result SET is_new = 0 WHERE is_new = 1; -- change the status in the result table so the names won't be moved to the tmp again

end while; 

SELECT name FROM result; -- displays the result table after recursive job is done

end//

delimiter ;

    Let’s test it using alice as the original leader:

    call test_proc('alice');

+----------+
| name     |
+----------+
| Bob      |
| Carol    |
| Dave     |
| Eve      |
| Frank    |
| Benjamin |
| Grace    |
+----------+

    Try helen this time:

    +--------+
| name   |
+--------+
| David  |
| Lucas  |
| Olivia |
| Sophia |
+--------+

    To put it in a nutshell, it’s possible to perform a recursive job without using a recursive CTE. But the coding is tedious. Should only do it when CTE is not available, and only as a last resort.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search