get parents and children of tree folder structure in my sql < 8 and no CTEs

In your table design, ID and PARENT_ID corresponds to the "Adjacency List Model" for storing a tree.

There is another design, called the "Nested Set Model", which makes it easier to perform the operations you want here.

See this excellent article from Mike Hillyer describing both:managing-hierarchical-data-in-mysql

In summary:

The tree is stored in a table like:

CREATE TABLE nested_category (        category_id INT AUTO_INCREMENT PRIMARY KEY,        name VARCHAR(20) NOT NULL,        lft INT NOT NULL,        rgt INT NOT NULL);

Finding the path from the root to a given node (here, 'FLASH'):

SELECT parent.nameFROM nested_category AS node,        nested_category AS parentWHERE node.lft BETWEEN parent.lft AND parent.rgt        AND node.name = 'FLASH'ORDER BY parent.lft;

Finding all children of a given node (here 'PORTABLE ELECTRONICS'):

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depthFROM nested_category AS node,        nested_category AS parent,        nested_category AS sub_parent,        (                SELECT node.name, (COUNT(parent.name) - 1) AS depth                FROM nested_category AS node,                        nested_category AS parent                WHERE node.lft BETWEEN parent.lft AND parent.rgt                        AND node.name = 'PORTABLE ELECTRONICS'                GROUP BY node.name                ORDER BY node.lft        )AS sub_treeWHERE node.lft BETWEEN parent.lft AND parent.rgt        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt        AND sub_parent.name = sub_tree.nameGROUP BY node.nameHAVING depth <= 1ORDER BY node.lft;

After renaming to your folders table

• TABLE nested_category -> TABLE folders
• Column category_id -> Column id
• Column name -> Column title

The solution is:

CREATE TABLE folders (        id INT AUTO_INCREMENT PRIMARY KEY,        title VARCHAR(20) NOT NULL,        lft INT NOT NULL,        rgt INT NOT NULL);INSERT INTO folders(id, title, lft, rgt) values(1, 'root', 1, 10);INSERT INTO folders(id, title, lft, rgt) values(2, 'one', 2, 9);INSERT INTO folders(id, title, lft, rgt) values(3, 'target', 3, 8);INSERT INTO folders(id, title, lft, rgt) values(4, 'child one', 4, 5);INSERT INTO folders(id, title, lft, rgt) values(5, 'child two', 6, 7);INSERT INTO folders(id, title, lft, rgt) values(6, 'root 2', 11, 16);INSERT INTO folders(id, title, lft, rgt) values(7, 'other child one', 12, 13);INSERT INTO folders(id, title, lft, rgt) values(8, 'other child two', 14, 15);

Path to the target:

SELECT parent.titleFROM folders AS node,        folders AS parentWHERE node.lft BETWEEN parent.lft AND parent.rgt        AND node.title = 'target'ORDER BY parent.lft;

Target children:

SELECT node.title, (COUNT(parent.title) - (sub_tree.depth + 1)) AS depth    FROM folders AS node,            folders AS parent,            folders AS sub_parent,            (              SELECT node.title, (COUNT(parent.title) - 1) AS depth                    FROM folders AS node,                            folders AS parent                    WHERE node.lft BETWEEN parent.lft AND parent.rgt                            AND node.title = 'target'                    GROUP BY node.title                    ORDER BY node.lft            )AS sub_tree    WHERE node.lft BETWEEN parent.lft AND parent.rgt            AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt            AND sub_parent.title = sub_tree.title    GROUP BY node.title    HAVING depth <= 1    ORDER BY node.lft;

See sqlfiddle

To get all the data in a single query, a union should do.

In MySQL 8.0, you can make use of the Recursive Common Table Expressions to adress this use case.

The following query gives you the parents of a given record (including the record itself):

with recursive parent_cte (id, title, parent_id) as (  select id, title, parent_id  from folders  where id = 3  union all  select  f.id, f.title, f.parent_id  from folders f  inner join parent_cte pc on f.id = pc.parent_id)select * from parent_cte;

| id  | title  | parent_id || --- | ------ | --------- || 3   | target | 2         || 2   | one    | 1         || 1   | root   |           |

And here is a slightly different query, that returns the children tree of a given record:

with recursive children_cte (id, title, parent_id) as (  select id, title, parent_id  from folders  where parent_id = 3  union all  select  f.id, f.title, f.parent_id  from folders f  inner join children_cte cc on f.parent_id = cc.id)select * from children_cte;

| id  | title     | parent_id || --- | --------- | --------- || 4   | child one | 3         || 5   | child two | 3         |

Both queriers can be combined as follows:

with recursive parent_cte (id, title, parent_id) as (  select id, title, parent_id  from folders  where id = 3  union all  select  f.id, f.title, f.parent_id  from folders f  inner join parent_cte pc on f.id = pc.parent_id),children_cte (id, title, parent_id) as (  select id, title, parent_id  from folders  where parent_id = 3  union all  select  f.id, f.title, f.parent_id  from folders f  inner join children_cte cc on f.parent_id = cc.id)select * from parent_cteunion all select * from children_cte;

| id  | title     | parent_id || --- | --------- | --------- || 3   | target    | 2         || 2   | one       | 1         || 1   | root      |           || 4   | child one | 3         || 5   | child two | 3         |

Demo on DB Fiddle

I've solved this in the past with a second table, which contains the transitive closure of all paths through the tree.

mysql> CREATE TABLE folders_closure ( ancestor INT UNSIGNED NOT NULL, descendant INT UNSIGNED NOT NULL, PRIMARY KEY (ancestor, descendant), depth INT UNSIGNED NOT NULL);

Load this table with tuples of all ancestor-descendant pairs, including the ones where a node in the tree references itself (path of length 0).

mysql> INSERT INTO folders_closure VALUES     (1,1,0), (2,2,0), (3,3,0), (4,4,0), (5,5,0), (6,6,0),     (1,2,1), (2,3,1), (3,4,1), (3,5,1), (1,4,2), (1,5,2),     (6,7,1), (6,8,1);

Now you can query the tree below a given node by querying all the paths that start at the top node, and join that path's descendant to your folders table.

mysql> SELECT d.id, d.title, cl.depth FROM folders_closure cl     JOIN folders d ON d.id=cl.descendant WHERE cl.ancestor=1;+----+-----------+-------+| id | title     | depth |+----+-----------+-------+|  1 | root      |     0 ||  2 | one       |     1 ||  4 | child one |     2 ||  5 | child two |     2 |+----+-----------+-------+

I see many people recommend the Nested Sets solution which was introduced in 1992, and became popular after Joe Celko included it in his book SQL for Smarties in 1995. But I don't like the Nested Sets technique, because the numbers aren't actually references to the primary keys of the nodes in your tree, and it requires renumbering many rows when you add or delete a node.

I wrote about the closure table method in What is the most efficient/elegant way to parse a flat table into a tree? and some of my other answers with the hierarchical-data tag.

I did a presentation about it: Models for Hierarchical Data.

I also covered this in a chapter of my book SQL Antipatterns: Avoiding the Pitfalls of Database Programming.