MySQL Hierarchical Queries – How to Get Full Tree Structure

MySQLquerystored-procedures

I want to get all data of tree by ID.

My table is:

ID     NAME     PARENT_ID
------------------------
1      A        NULL
2      B        1
3      C        1
4      D        3
5      E        4
6      F        NULL
7      G        6
8      H        NULL

I want the output result based on the following:

Input(ID)       Output(IDs)
-----------------------
1               2,3,4,5
2               1,3,4,5
3               1,2,4,5
4               1,2,3,5
5               1,2,3,4
6               7
7               6
8               NULL

How can i do that?

Best Answer

Procedure code

DELIMITER @;

DROP PROCEDURE IF EXISTS GetBranch@;
CREATE PROCEDURE GetBranch()
BEGIN
DROP TABLE IF EXISTS temp;
CREATE TABLE temp (id INT, parent_id INT, UNIQUE (id, parent_id)) ENGINE=Memory;
INSERT INTO temp (id, parent_id) 
    SELECT id, COALESCE(parent_id, id) FROM test;
INSERT IGNORE INTO temp (id, parent_id) 
    SELECT parent_id, id FROM temp;
REPEAT
    SELECT COUNT(*) INTO @count1 
        FROM temp;
    INSERT IGNORE INTO temp
        SELECT t1.id, t2.parent_id
        FROM temp t1, temp t2
        WHERE t2.id = t1.parent_id; 
    SELECT COUNT(*) INTO @count2 
        FROM temp;
UNTIL @count1 = @count2 END REPEAT;
UPDATE temp 
    SET parent_id = NULL 
    WHERE id = parent_id;
DELETE t1.* 
    FROM temp t1, temp t2 
    WHERE t1.id = t2.id 
      AND t1.parent_id IS NULL 
      AND t2.parent_id IS NOT NULL;
SELECT id, GROUP_CONCAT(parent_id ORDER BY parent_id) branch 
    FROM temp 
    GROUP BY id 
    ORDER BY id;
DROP TABLE temp;
END;
@;

DELIMITER ;

Test code

DROP TABLE IF EXISTS test;
CREATE TABLE test (id INT, name CHAR(1), parent_id INT);
INSERT INTO test VALUES
    (1,'A',NULL),
    (2,'B',1),
    (3,'C',1),
    (4,'D',3),
    (5,'E',4),
    (6,'F',NULL),
    (7,'G',6),
    (8,'H',NULL);

CALL GetBranch;

DROP TABLE test;

Test output

mysql> CALL GetBranch;
+------+---------+
| id   | branch  |
+------+---------+
|    1 | 2,3,4,5 |
|    2 | 1,3,4,5 |
|    3 | 1,2,4,5 |
|    4 | 1,2,3,5 |
|    5 | 1,2,3,4 |
|    6 | 7       |
|    7 | 6       |
|    8 | NULL    |
+------+---------+
8 rows in set (0.31 sec)
Related Question