CODEWITHSUNDEEP
mysql
Randy
Randy

Reputation: 1136

MySQL Get All Children in a Column

I have a MySQL table that looks something like this:

----------------------
| ID | Name | Parent |
----------------------
| 1  |  a   | null   |
| 2  |  b   | null   |
| 3  |  c   | 1      |
| 4  |  d   | 3      |
| 5  |  e   | 2      |
| 6  |  f   | 2      |
----------------------

with an unknown number of possible depth to the parent/child relationship.

I want a query that will give me the following result:

-----------------
| ID | Children |
-----------------
| 1  | 3,4      | -- because 4 is a child of 3, and 3 is a child of 1, it should show in both
| 2  | 5,6      |
| 3  | 4        |
| 4  | null     |
| 5  | null     |
| 6  | null     |
-----------------

Is it possible to get this kind of result in a query? I've been trying everything I know to try, and searching everywhere and have not found something that will give me this result.

Upvotes: 2

Views: 791

Answers (1)

Dark Knight
Dark Knight

Reputation: 6531

Considering out table as employees with fields ID, Name, Parent.

Approach 1: When we know the depth of our hierarchy We can simply join our table n time equal to our hierarchy and can use GROUP BY to get the desired results. Here it is 3 so

SELECT t1.ID AS lev1,  GROUP_CONCAT(CONCAT_WS(',', t2.ID, t3.ID)) AS childs
FROM employees AS t1
LEFT JOIN employees AS t2 ON t2.Parent = t1.ID
LEFT JOIN employees AS t3 ON t3.Parent = t2.ID
GROUP BY t1.ID

Corresponding fiddle you can look here.

Approach 2: When we don't know the depth of our hierarchy

We'll create two procedures for that.

  1. store_emp_childs - It will store parent and its child in a temporary table.
  2. get_emp_child - It will create temp table, call store_emp_childs to generate resultset. Select(return) the data from temporary table and remove the temporaray table.
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_emp_child`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

    CREATE TEMPORARY  TABLE `tmp_emp_child` (
        `emp_id` INT(11) NOT NULL,
        `child_id` INT(11) NOT NULL,
        PRIMARY KEY (`emp_id`, `child_id`)
    );
    
    CALL store_emp_childs(NULL, '');
    
    SELECT e.ID, GROUP_CONCAT(ec.child_id) AS childs
    FROM employees e
    LEFT JOIN tmp_emp_child ec ON e.ID = ec.emp_id
    GROUP BY e.ID;
    
    DROP TEMPORARY TABLE tmp_emp_child;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `store_emp_childs`(
    IN `int_parent` INT,
    IN `old_parents` VARCHAR(100)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE finished, current_id INTEGER DEFAULT 0;
    DEClARE cur CURSOR FOR SELECT id FROM employees WHERE IFNULL(Parent, 0) = IFNULL(int_parent, 0);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN cur;

    curID: LOOP
        FETCH cur INTO current_id;
        IF finished = 1 THEN 
            LEAVE curID;
        END IF;
        
        INSERT INTO tmp_emp_child (emp_id, child_id)
        SELECT id, current_id FROM employees WHERE FIND_IN_SET(id, old_parents) OR id = int_parent
        ON DUPLICATE KEY UPDATE emp_id = emp_id;
        
        CALL store_emp_childs(current_id, CONCAT(old_parents, ',', current_id));
    END LOOP curID;
    CLOSE cur;
END

Note:

  1. We are recursively calling our store_emp_childs. It requires max_sp_recursion_depth parameter to be set more than 0. I recommend to make it 250. It will not work if records are more than this recursion depth. Will look further to improve this.
  2. We are creating temporary table, so user should have rights to create that.

Upvotes: 5

Related Questions