find out list of inverted hierarchy in mysql

Question

Imagine I have a table (Mysql myISAM) with a child->parent relationship (categories and multiple levels of subcategories)

+--------+---------+
| id     |parent_id|
+--------+---------+
| 1      |  null   |
| 2      |    1    |
| 3      |    2    |
| 4      |    7    |
| 5      |    1    |
| 6      |    5    |
+--------+---------+

How would you find all children of some ID, like querying for id 1 would output : 2,5,3,6 ? (order has no importance)

So in other words, how to do a reverted children lookup on this parent_link ?

At the moment, I cycle in php and query for the parent_id, then again and concatenate all the results in a string while there are results, but this is so slow...

Why are the results 4,1,5,6 and not 4,1,3,2,4,1,3,.... It seems to me that 1 is connected to both 5 and 3, no? — Gidil, Oct 06 '13 at 13:43
See this http://stackoverflow.com/questions/12796113/mysql-how-to-find-leaves-in-specific-node/12797585#12797585 — AgRizzo, Oct 06 '13 at 13:48
@Gidil, sorry just some random data, you're right, fixed this — Vincent Duprez, Oct 06 '13 at 13:49

score 1 · Answer 1 · answered Oct 06 '13 at 17:17

Ok, so thanks to Deepak code, I managed to write this, a bit shorter readable, it accepts a table as parameter and returns also the depth of the element.

DELIMITER $$

CREATE PROCEDURE get_children(IN V_KEY INT,IN SOURCETABLE VARCHAR(255))
proc:
BEGIN
  DECLARE vid text;
  DECLARE count int;

  DROP TABLE IF EXISTS `temp_child_nodes`;
  CREATE TEMPORARY TABLE temp_child_nodes(id int, depth int);

  SET vid = V_KEY;
  SET @val = '';
  SET count = 0;

  WHILE (vid is NOT NULL) DO 

      SET @sql = CONCAT("INSERT INTO temp_child_nodes(id,depth) SELECT id,'",count,"' from ",SOURCETABLE," where parent_id IN (",vid,")");
      PREPARE stmt1 FROM @sql;
      EXECUTE stmt1;
      DEALLOCATE PREPARE stmt1;


      SET @tsql = CONCAT("SELECT GROUP_CONCAT(id) INTO @val from ",SOURCETABLE," where parent_id IN (", vid, ")");
      PREPARE stmt2 FROM @tsql;
      EXECUTE stmt2;
      DEALLOCATE PREPARE stmt2;
      SET vid = @val;

      SET count = count + 1;
  END WHILE;

  #output data
  SELECT * from temp_child_nodes; 

END
$$

DELIMITER ;

score 0 · Answer 2 · answered Oct 06 '13 at 13:55

0

Here is the sqlfiddle demo for your query http://sqlfiddle.com/#!2/ca90e/6

if there can be 'n' number of child then you need to use a stored procedure

answered Oct 06 '13 at 13:55

Deepak Rai

2,163
3
21
36

@VincentDuprez : Please refer to my second answer. The stored procedure will get you children of any id till any depth – Deepak Rai Oct 06 '13 at 14:26

score 0 · Accepted Answer · answered Oct 06 '13 at 14:25

create table my_table(
id int,
parent_id int
);

insert into my_table values
(1,null),
(2,1),
(3,2),
(4,7),
(5,1),
(6,5);

This stored procedure will get you all the children of any given id

DELIMITER $$
DROP PROCEDURE IF EXISTS get_children$$

CREATE PROCEDURE get_children(IN V_KEY INT)
proc:
BEGIN
  DECLARE vid text;
declare oid text;
  DECLARE count int;
  CREATE TEMPORARY TABLE temp_child_nodes(
      id int
    );

  SET vid = V_KEY;
  INSERT INTO temp_child_nodes(id) SELECT id from my_table where parent_id = vid;
  SELECT GROUP_CONCAT(concat("'",id,"'")) INTO oid from my_table where parent_id = vid;

  SET vid = oid;
  SET count = 0;
  SET @val = '';
  WHILE (vid is NOT NULL) DO 

      SET @sql = CONCAT("INSERT INTO temp_child_nodes(id) SELECT id from my_table where parent_id IN (",vid,")");
      PREPARE stmt1 FROM @sql;
      EXECUTE stmt1;
      DEALLOCATE PREPARE stmt1;

      SET @tsql = CONCAT("SELECT GROUP_CONCAT(id) INTO @val from my_table where parent_id IN (", vid, ")");
      PREPARE stmt2 FROM @tsql;
      EXECUTE stmt2;
      DEALLOCATE PREPARE stmt2;
      SET vid = @val;
      SET count = count + 1;
  END WHILE;
  #SELECT count;
  SELECT * from temp_child_nodes; 
  #SELECT oid;
END
$$

DELIMITER ;

CALL get_children(1);

mysql> CALL get_children(1);
+------+
| id   |
+------+
|    2 |
|    5 |
|    3 |
|    6 |
+------+
4 rows in set (0.22 sec)

Query OK, 0 rows affected (0.22 sec)

Works! I'm trying to modify it to get more info in one call (like depth) and add an additional parameter to set table, I'll send it here if I succeed — Vincent Duprez, Oct 06 '13 at 14:53

find out list of inverted hierarchy in mysql

3 Answers3