tree branch from mysql

Question

I have mysql table with schema whixh contain data to store tree structure.

CREATE TABLE `treedata` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) unsigned NOT NULL DEFAULT '0',
  `depth` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `name` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniquecheck` (`parent_id`,`name`) USING BTREE,
  KEY `depth` (`depth`) USING BTREE,
  KEY `parent_id` (`parent_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1

It has below data.

mysql> select * from treedata;
+----+-----------+-------+------+
| id | parent_id | depth | name |
+----+-----------+-------+------+
|  1 |         1 |     0 | root |
|  2 |         1 |     1 | b1   |
|  3 |         1 |     1 | b2   |
|  4 |         1 |     1 | b3   |
|  5 |         2 |     2 | b1_1 |
|  6 |         2 |     2 | b1_2 |
|  7 |         2 |     2 | b1_3 |
|  8 |         3 |     2 | b2_1 |
|  9 |         3 |     2 | b2_2 |
| 10 |         3 |     2 | b2_3 |
| 11 |         4 |     2 | b3_1 |
| 12 |         4 |     2 | b3_2 |
| 13 |         4 |     2 | b3_3 |
+----+-----------+-------+------+
13 rows in set (0.00 sec)

I need to select branch and its children based on depth and name, like if depth is 1 and name is b1 then it should return

+----+-----------+-------+------+
| id | parent_id | depth | name |
+----+-----------+-------+------+
|  2 |         1 |     1 | b1   |
|  5 |         2 |     2 | b1_1 |
|  6 |         2 |     2 | b1_2 |
|  7 |         2 |     2 | b1_3 |
+----+-----------+-------+------+

I am new to database. I tried left join it gives all children but not branch itself.

mysql> select td2.* from treedata as td1 left join treedata as td2 on td1.id=td2.parent_id where td1.name='b1';
+------+-----------+-------+------+
| id   | parent_id | depth | name |
+------+-----------+-------+------+
|    5 |         2 |     2 | b1_1 |
|    6 |         2 |     2 | b1_2 |
|    7 |         2 |     2 | b1_3 |
+------+-----------+-------+------+
3 rows in set (0.00 sec)

Note: I can't change database schema.

You're looking for a hierarchical query. Which version of MySQL are you using? https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — Neville Kuyt, Aug 11 '20 at 07:42

score 0 · Answer 1 · answered Jun 24 '20 at 05:15

0

you can use like cluse for select all data which has b1 branch like this .

select td2.* from treedata as td1 left join treedata as td2 on td1.id=td2.parent_id where td1.name LIKE '%b1%';

answered Jun 24 '20 at 05:15

Arpit Vyas

2,118
1
7
18

Doesn't seems working. And name are just informative. children may not contain name of parent. – Manthan Tilva Jun 24 '20 at 05:46
what you get from this query? – Arpit Vyas Jun 24 '20 at 05:47

score 0 · Answer 2 · answered Aug 11 '20 at 07:37

i think it may help you

select  * from (select * from table_name order by `depth`) products_sorted,(select @pv := 'your_node_id(string)') initialisation where (find_in_set(parent_id, @pv) or id=your_node_id) and length(@pv := concat(@pv, ',', id))

it will find all children of your starting node

tree branch from mysql

2 Answers2