CTE, why is an empty table returned?

Question

I'm using a Postgresql database and I'm trying to get a tree using a CTE:

Alice
...Bob
......Dave
......Emma
...Cindy
......Fred
......Gail

Doesn't output any errors. There is nothing in the output, where did I go wrong?

Database

CREATE TABLE org(
  name TEXT PRIMARY KEY,
  boss TEXT REFERENCES org
);

INSERT INTO org VALUES('Alice',NULL);
INSERT INTO org VALUES('Bob','Alice');
INSERT INTO org VALUES('Cindy','Alice');
INSERT INTO org VALUES('Dave','Bob');
INSERT INTO org VALUES('Emma','Bob');
INSERT INTO org VALUES('Fred','Cindy');
INSERT INTO org VALUES('Gail','Cindy');

My request:

WITH RECURSIVE under_alice AS (select name, boss, 0 AS level FROM org 
     where boss = null
    UNION ALL
    SELECT org.name, org.boss, under_alice.level+1
      FROM org JOIN under_alice ON org.boss=under_alice.name
  )
SELECT * FROM under_alice;

Which RDBMS are you using (MySQL, Postgres, Oracle, SQL Server, something else)? Please add the tag of your database to your question. See [this](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms) to know why tagging is important. — Bohemian, Apr 19 '22 at 07:28

score 3 · Accepted Answer · answered Apr 19 '22 at 07:33

WITH RECURSIVE under_alice AS
(
  select name, boss, 0 AS level FROM org 
     where boss IS NULL 
 UNION ALL
  SELECT org.name, org.boss, under_alice.level+1
  FROM org JOIN under_alice ON org.boss=under_alice.name
)
SELECT * FROM under_alice

Please take a look on where boss IS NULL. Here is the issue in your query

CTE, why is an empty table returned?

1 Answers1