Find the hierarchy tree of parent and childs and their childs - MySQL Query

Question

I have a table with different zone names with Id's and their parent id's. I want to generate report with the hierarchies. Like below.

Table: Groups

ID                Name              ParentID
1              Corporate               NULL
2              Zone 1                   1
3              Zone 2                   1
4              Zone 3                   1
5              Zone 4                   1
6             Telangana                 2
7             Hyderabad                 6
8             Khammam                   6
9             Odisha                    3
10            Bhubaneshwar              9

Using above table now I want to generate report. If I select corporate then I need to get all data. If I select Zone 1 I need to get all child relations as well.Like Below

Zone 1, Telangana, Hyderabad, Khammam

Please help me on writing query for this.

Answer 1

I've found the answer for my above question. This can be achieved by using Stored Procedure.

DELIMITER $$
DROP PROCEDURE IF EXISTS getHierarchy_proc $$
CREATE PROCEDURE getHierarchy_proc (IN GivenID INT, OUT ids VARCHAR(10000))
BEGIN
DECLARE int_check VARCHAR(1000);
DECLARE is_exit TINYINT(1) DEFAULT 0;

DROP TABLE IF EXISTS bu_tmp;
CREATE TEMPORARY TABLE bu_tmp(
bu_id INT(11) NOT NULL,
is_upd TINYINT(1) NOT NULL DEFAULT 0);

SET SESSION GROUP_CONCAT_MAX_LEN = 100000;

INSERT INTO bu_tmp (bu_id) 
SELECT GivenID;

  SET int_check = (SELECT bu_id FROM bu_tmp WHERE bu_id = GivenID AND is_upd = 0);
  SET is_exit = 1;
  REPEAT 

    IF is_exit > 0 THEN 

        INSERT INTO bu_tmp (bu_id,is_upd)
        SELECT ID,0 FROM Groups WHERE FIND_IN_SET(parent_id , int_check);
        UPDATE bu_tmp SET is_upd = 1 WHERE FIND_IN_SET(bu_id,int_check);
        SET is_exit = (SELECT COUNT(*) FROM bu_tmp WHERE is_upd = 0);
        SET int_check = (SELECT GROUP_CONCAT(bu_id) FROM bu_tmp WHERE is_upd = 0);

    END IF; 
    UNTIL is_exit = 0 END REPEAT;

SET ids = (SELECT GROUP_CONCAT(lew.le_wh_id)
         FROM bu_tmp bu JOIN legalentity_warehouses lew
         WHERE bu.bu_id = lew.bu_id
         AND lew.dc_type = 118001
         AND lew.status  = 1);       
END$$
DELIMITER ;