How can I make recursive query in SQL?

Question

I have tow tables like

users

id | name
_______________
1  | one
2  | two
3  | three
4  | four
5  | five
6  | six

employees

id | userId | reportedTo
_________________________
1  | 1      |   null
2  | 2      |   1
3  | 3      |   2
4  | 4      |   3
5  | 5      |   4
6  | 6      |   5

I need something like

if I run query for some user then it will return all the record which reportedTo is this user and other users which reportedTo is reportedTo this user

Like:

if I run this query for user 1 then it will return all the record accept userId 1 from employees

id | userId | reportedTo
    _________________________
    2  | 2      |   1
    3  | 3      |   2
    4  | 4      |   3
    5  | 5      |   4
    6  | 6      |   5

if I run query for userId 4 then it will return recornds with userId 5 and 6 from employee table

id | userId | reportedTo
    _________________________
    5  | 5      |   4
    6  | 6      |   5

can anyone help me with this.

Thanks in advance

Answer 1

You can use a recursive CTE:

with recursive cte(i, u, r) as (
   select * from employees where reportedTo = 1
   union all
   select e.* from cte c join employees e on c.u = e.reportedTo
)
select c.i id, c.u userId, c.r reportedTo from cte c;

Output:

id	userId	reportedTo
2	2	1
3	3	2
4	4	3
5	5	4
6	6	5

See demo