Solution to find all child rows for a given parent row in MySQL

Preface

Note: The test database version is MySQL 8.0

If you need to create tables and enter data under user scott, you can refer to:

Scott creates tables and enters data SQL script

1. Demand

Find all employees who work for JONES directly and indirectly (i.e., subordinates of JONES' subordinates).

The list of employees under JONES is as follows:

±------±-----+
| ename | lvl |
±------±-----+
| JONES | 1 |
| SCOTT | 2 |
| FORD | 2 |
| ADAMS | 3 |
| SMITH | 3 |
±------±-----+

2. Solution

Being able to move to the absolute top and bottom of a number is very useful.

No special formatting is required for this solution. The goal is simply to return all employees who are under employee JONES, which includes JONES himself.

This type of query demonstrates the usefulness of recursive SQL extensions such as Oracle's connect by and SQL Server/DB 2/MySQL 8.0's with clause.

with recursive emp2(ename,empno,lvl) as
(
SELECT ename,empno,1 lvl
 from emp 
 where ename = 'JONES'
union ALL
select e1.ename,e1.empno,lvl + 1
 from emp e1,emp2 e2
 where e1.mgr = e2.empno
)
select ename,lvl from emp2

Testing Log:

mysql> with recursive emp2(ename,empno,lvl) as
 -> (
 -> SELECT ename,empno,1 lvl
 -> from emp
 -> where ename = 'JONES'
 -> union ALL
 -> select e1.ename,e1.empno,lvl + 1
 -> from emp e1,emp2 e2
 -> where e1.mgr = e2.empno
 -> )
 -> select ename,lvl from emp2;
+-------+------+
| ename | lvl |
+-------+------+
| JONES | 1 |
| SCOTT | 2 |
| FORD | 2 |
| ADAMS | 3 |
| SMITH | 3 |
+-------+------+
5 rows in set (0.01 sec)

Summarize

This is the end of this article about finding all child rows for a given parent row in MySQL. For more information about finding all child rows for a given parent row in MySQL, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!