Find the level segments up-to 7-levels in parent-child hierarchy

Question

Parent-child table

id	introducer_id	name
1	NULL	Riya
2	1	Ramesh
3	1	Anand
4	2	Preety
5	3	Rakesh

Query to get members list when id = 1

select  id AS memberid, name 
from (select * 
      from table_member 
      order by introducer_id, id) table_member_sorted,
     (select @pv := '1') initialisation 
where find_in_set(introducer_id, @pv) 
  and length(@pv := concat(@pv, ',', id))

The above query displays output

member_id	name
2	Ramesh
3	Anand
4	Preety
5	Rakesh

Now i also want to find the level of all the members (i.e) i want the output as:

member_id	name	level
2	Ramesh	1
3	Anand	1
4	Preety	2
5	Rakesh	2

How can i update my previous query to get the above output?

Note: Query to display maximum level upto 7

Thank You.

Answer 1

CREATE PROCEDURE get_tree (start_from INT)
BEGIN
    DROP TABLE IF EXISTS tmp;
    CREATE TABLE tmp (member_id INT PRIMARY KEY, 
                      name VARCHAR(255),
                      level INT);
    INSERT INTO tmp SELECT id, name, 0
                    FROM test
                    WHERE id = start_from;
    REPEAT
        INSERT IGNORE INTO tmp SELECT test.id, test.name, tmp.level + 1
                               FROM test
                               JOIN tmp ON tmp.member_id = test.introducer_id;
    UNTIL !ROW_COUNT() END REPEAT;
    SELECT * FROM tmp;
END

fiddle

---

I have shared screenshot link of my sql version. snipboard.io/qbdB0A.jpg – AfreenB

Your SQL server version is MariaDB 10.4.14. – Akina

For your server version use

WITH RECURSIVE
cte AS ( SELECT id member_id, name, 0 level
         FROM test
         WHERE id = @start_from 
       UNION ALL
         SELECT test.id, test.name, cte.level + 1
         FROM test
         JOIN cte ON cte.member_id = test.introducer_id 
      -- WHERE cte.level < 7 )
SELECT * 
FROM cte;

fiddle