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
But it still return rows where parent.is_public = 1;
What I am doing wrong?