How to Retrieve All Child and Subchild Folders in a Hierarchical Database using a Single SQL Query?

Recursive Query to Retrieve All Child and Subchild Folders in MySQL

Question:

How can I design a single SQL query to retrieve all child and subchild folders for a given parent folder ID in a hierarchical database?

Scenario:

Consider a table Folder with the following structure:

CREATE TABLE IF NOT EXISTS `Folder` (
    `idFolder` INT(11) NOT NULL AUTO_INCREMENT,
    `FolderName` VARCHAR(150) NOT NULL,
    `idFolderParent` INT(11) NULL,
    PRIMARY KEY (`idFolder`),
    CONSTRAINT `fk_1`
    FOREIGN KEY (`idFolderParent`)
    REFERENCES `Folder` (`idFolder`)
);

The table is populated as follows:

idFolder	FolderName	idFolderParent
1	ADoc	NULL
2	ADoc1	1
3	ADoc2	2
4	ADoc3	3
5	ADoc4	4
6	ADoc5	5
7	ADoc6	4

Problem Statement:

We want to design a query that returns all child and subchild folders for a given parent folder ID. For example:

• For parent folder ID 1, the query should return [2, 3, 4, 5, 6, 7].
• For parent folder ID 4, the query should return [5, 7, 6].
• For parent folder ID 3, the query should return [4, 5, 6, 7].

Solution:

The following query uses a recursive technique to achieve the desired result:

<code class="sql">SELECT GROUP_CONCAT(lv SEPARATOR ',') 
FROM (
    SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder 
    WHERE FIND_IN_SET(idFolderParent, @pv)) AS lv 
    FROM Folder 
    JOIN (SELECT @pv:=5) tmp
) a;</code>

Explanation:

• The query uses a user-defined variable @pv to store the comma-separated list of child folder IDs for the current level.
• The subquery in the SELECT statement retrieves the child folder IDs for the current level, separating them with commas.
• The outer query then concatenates the results of all levels, providing a comma-separated list of all child and subchild folder IDs for the specified parent.

The above is the detailed content of How to Retrieve All Child and Subchild Folders in a Hierarchical Database using a Single SQL Query?. For more information, please follow other related articles on the PHP Chinese website!