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

Thank you very much for including the CREATE TABLE and some sample data in your question. This is the best way to ask SQL questions, and too few people include these needed details in their questions. — Bill Karwin, Nov 25 '17 at 20:32
Thank you, Bill. I am seeing you are an extreme expert and therefore this is an extremely pleasant to resort to stackoverflow. Thank you so very much. — Rajib Hossain Pavel, Nov 25 '17 at 20:34

score 2 · Accepted Answer · answered Nov 25 '17 at 20:26

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 |
+----------------------------------+

MySQL Subquery Looping for each row returned

1 Answers1