SQL, Recursive query to get ancestors with some rules of all parents

56 views Asked by At
ID    parent_id          name       is_public
------------------------------------------------
1        null         category 1        0
2        1            category 2        0
3        2            category 3        0
4        3            category 4        0
5        4            category 5        1

I need return rows where is_public = 0 and all ancestors is_public = 0 too In this case it should return ID: 1, 2, 3, 4

But for example if I set ID = 1 is_public = 1 it shouldn't return any row.

I have tried SQL like:

WITH RECURSIVE recursiveTree AS (
      SELECT task_categories.id, task_categories.name, task_categories.is_public
      FROM task_categories WHERE task_categories.is_public = 0

      UNION ALL
      SELECT tc.id, tc.name, tc.is_public
      FROM recursiveTree
      INNER JOIN task_categories tc ON recursiveTree.id = tc.parent_id
      WHERE tc.is_public = 0
    )
    SELECT DISTINCT * FROM recursiveTree WHERE recursiveTree.is_public = 0

https://dbfiddle.uk/MOpfujWT

But it still return rows where parent.is_public = 1;

What I am doing wrong?

0

There are 0 answers