Why does my recursive query stop after one step?

Question

I would like to get a list of employees in an organization that ultimately report to a given person (a flattened sub-tree of the org chart). I created a sample organization (in SQLite, view on DB Fiddle) with john as the boss, who has two reports (luc and bob) and bob has one report (steve).

In order to seed the recursive query, I want to use the nickname (and get a list of nickname) but the relationship between the people is based on their login.

create table users (
  login text,
  nickname text,
  manager text
  );
  
 insert into users values ('john', 'j',  NULL), ('luc', 'l', 'john'), ('bob', 'b', 'john'), ('steve', 's', 'bob');

I created a recursive query

WITH orgtree AS (
select login  from users where users.nickname='j'

UNION all
    
SELECT users.nickname FROM users, orgtree
WHERE users.manager=orgtree.login
)

SELECT users.nickname FROM users
WHERE users.nickname IN orgtree ;

What I get as a result is just the "first line" of j (of john):

nickname
l
b

What am I missing in my query to go deeper? (in either the anchor or the recursive member?)

Answer 1

The immediate problem is that the recursive member of the CTE returns the user's nickname, while you need their login to be able to look up the next hierarchical level. A direct fix is:

with orgtree as (
    select login  from users where users.nickname='j'
    union all
    select users.login 
    from users, orgtree
    where users.manager=orgtree.login
)
select nickname from users where login in orgtree;

But we can further improve the query, by keeping track of both the nickname and the login in the recursive, so there is no need for in in the outer query:

with orgtree as (
    select nickname, login from users where nickname = 'j'
    union all
    select u.nickname, u.login 
    from users u
    inner join orgtree o on u.manager = o.login
)
select nickname from orgtree

Note that I used standard, explicit joins rather than implicit, old-school joins.

Demo on DB Fiddle

nickname
j
b
l
s