MySQL Subquery Looping for each row returned

Question

This uses two queries and I like to get result for each rows returned in the first subquery that will run the second query, essentially this will act like a foreach loop. I hope there is someone who can figure this out quite quickly.

CREATE TABLE `File_Structure`(
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL DEFAULT '',
    `is_folder` TINYINT(1) NOT NULL DEFAULT 0,
    `lft` INT NOT NULL DEFAULT 0,
    `rht` INT NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `modified` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
)ENGINE=InnoDB;
INSERT INTO `file_structure` (`id`, `name`, `is_folder`, `lft`, `rht`, `is_active`, `created`, `modified`) VALUES
    (1, 'Code', 1, 1, 12, 1, '2017-11-26 00:13:40', '0000-00-00 00:00:00'),
    (2, 'Account', 1, 2, 7, 1, '2017-11-26 00:13:40', '0000-00-00 00:00:00'),
    (3, 'AccountCreate.php', 0, 3, 4, 1, '2017-11-26 00:13:40', '0000-00-00 00:00:00'),
    (4, 'AccountsFindAll.php', 0, 5, 6, 1, '2017-11-26 00:13:40', '0000-00-00 00:00:00'),
    (5, 'JournalEntry', 1, 8, 11, 1, '2017-11-26 00:13:41', '0000-00-00 00:00:00'),
    (6, 'JournalCreate.php', 0, 9, 10, 1, '2017-11-26 00:13:41', '0000-00-00 00:00:00');

-- 1. FIND ALL LEAF NODES OF A DESIRED NODE.
SELECT `name`
FROM `File_Structure` AS `MAIN`,
(
    SELECT `lft`, `rht`
    FROM
    `File_Structure`
    WHERE `name`='Account'
) AS `SUB`
WHERE `MAIN`.`lft`>`SUB`.`lft`
AND `MAIN`.`rht`<`SUB`.`rht`
AND `MAIN`.`rht`=`MAIN`.`lft`+1
;
-- 2. FOR A LEAF NODE, GET A PATH UPTO THE DESIRED NODE.
//SET group_concat_max_len = 2048 TO AVOID THE LIMIT OF 1K STRING LENGTH.

SELECT GROUP_CONCAT(`SUB`.`name` SEPARATOR '/') AS `CONCATEDNAME`
FROM
(
    SELECT `PARENT`.`name`
    FROM `File_Structure` AS `NODE`,
    `File_Structure` AS `PARENT`
    WHERE `NODE`.`lft` BETWEEN `PARENT`.`lft` AND `PARENT`.`rht`
    AND `NODE`.`name` = 'AccountsFindAll.php'
    ORDER BY `PARENT`.`lft`
) AS `SUB`
;

-- 3. REPEAT 2 FOR 1

Answer 1

It's much easier to use JOIN than various levels of subqueries:

SELECT GROUP_CONCAT(path.name ORDER BY path.lft SEPARATOR '/' ) AS CONCATEDNAME
FROM File_Structure AS root
JOIN File_Structure AS leaf ON leaf.lft BETWEEN root.lft AND root.rht
JOIN File_Structure AS path ON leaf.lft BETWEEN path.lft AND path.rht
WHERE leaf.rht = leaf.lft+1
AND root.name='Account'
GROUP BY leaf.name;

Output:

+----------------------------------+
| CONCATEDNAME                     |
+----------------------------------+
| Code/Account/AccountCreate.php   |
| Code/Account/AccountsFindAll.php |
+----------------------------------+