Home >Database >Mysql Tutorial >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:
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 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!