Jan 7, 2013

MYSql query to select parent child recursive

There are many scenarios in which we need to get all the child of parent in nested parent child relation. That is child can have also child and so on. MySql has not any concept of recursive common table expression like other databases. So it is little difficult to get the entire child list.  In this approach we are doing in two steps.

Step 1:  We shall get all the child of given parents in a comma delimited string.

Step 2: We shall use the output of step 1 in the main query in the IN clause.

Step 1:
Let assume we have table say tblParentChild and we are inserting some records into it.

CREATE TABLE tblParentChild(
     ntParentID BIGINT,
     ntChildID bigint
);

INSERT INTO tblParentChild
VALUES(1,2),(1,3),(4,5),(4,6),(2,7),(2,8),(3,9),(10,11),(11,12),(12,13);

Mysql script to get the parent child recursive:

DECLARE vcChildList LONGTEXT DEFAULT '1,10';
DECLARE vcTempChildList LONGTEXT;
DECLARE vcTempParentList LONGTEXT;

SET vcTempParentList = vcChildList;

WHILE vcTempParentList IS NOT NULL DO

     SELECT GROUP_CONCAT(ntChildID) INTO vcTempChildList
     FROM tblParentChild
    WHERE FIND_IN_SET(ntParentID,vcTempParentList);

     SELECT vcTempChildList,vcTempParentList,vcChildList;
     IF vcTempChildList IS NOT NULL THEN

          SET vcChildList = CONCAT(vcChildList,',',vcTempChildList);
          SET vcTempParentList = vcTempChildList;
          SET vcTempChildList = NULL;

     ELSE

          SET vcTempParentList = NULL;

     END IF;
    
END WHILE;

SELECT vcChildList;

Sample output of above script: 1,10,2,3,11,7,8,9,12,13

Step 2: Use parent child recursive list in the main query. For example:


WHERE FIND_IN_SET(ID,'1,10,2,3,11,7,8,9,12,13')

No comments:

Post a Comment