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

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

Barbara Streisand
Barbara StreisandOriginal
2024-11-02 03:00:30449browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn