RECURSIVE query for Postgres on a single table

Question

I want to create one RECURSIVE query on a single table in Postgres, which is basically base on Parent and child.

Here is the demo table employee with data

id parentid managerid status
------------------------------------
3741 [null] 1709 7 
3742 3741 1709 12 
3749 3742 1709 12 
3900 3749 1709 4

1) If Status = 12 then the result will be, the data which has status = 12 and all the parents of that particular node.

The expected result will be :

 id parentid managerid status
--------------------------------------
 3741 [null] 1709 7 
 3742 3741 1709 12 
 3749 3742 1709 12 

For that I have tried the query which is given below is working fine and giving proper result, even if I change the status value than also its working fine.

WITH RECURSIVE nodes AS (
 SELECT s1.id, case when s1.parentid=s1.id then null else s1.parentid end parentid,s1.managerid, s1.status
 FROM employees s1 WHERE id IN 
 (SELECT employees.id FROM employees WHERE 
 "employees"."status" = 12 AND "employees"."managerid" = 1709)
 UNION ALL
 SELECT s2.id, case when s2.parentid=s2.id then null else s2.parentid end parentid,s2.managerid, s2.status
 FROM employees s2 JOIN nodes ON s2.id = nodes.parentid
)
SELECT distinct nodes.id, nodes.parentid, nodes.managerid, nodes.status 
 FROM nodes ORDER BY nodes.id ASC NULLS FIRST;

2) If Status != 12 then the result will be, only all the parents of that particular node.

The expected result will be :

 id parentid managerid status
--------------------------------------
 3741 [null] 1709 7 

I want the query for status not equal some value.

Answer 1

WITH RECURSIVE cte AS (
 SELECT * FROM tablename
 WHERE status != 12
 UNION
 SELECT t.* 
 FROM tablename t INNER JOIN cte c
 ON c.parentid = t.id
)
SELECT DISTINCT * FROM cte;

For more refer the Demo: demo

Answer 2

This is a very simple solution but I think it should work for smaller sets of data

 SELECT * FROM employee
 WHERE 
 status=12
 OR id IN (
 SELECT DISTINCT parentId FROM employee WHERE status=12
 )
`

• Jinal Tailor: It won't work because the status value may vary. Your query is giving result which is proper only for status = 12, it's not working proper even i keep status = 4 in your query.
• Leela Venkatesh K: I am actually using the same status twice in the query. Please confirm both are updated everytime
• Jinal Tailor: yes I have updated at both place. for more information refer link. We need working flow like that. In this link the demo is provided, we want to filter on particular column as shown in link. and i actually need the query to apply filter on column which is applied in demo..

Answer 3

With this recursive CTE:

with recursive cte as (
 select * from tablename
 where status = 12
 union all
 select t.* 
 from tablename t inner join cte c
 on c.parentid = t.id
)
select distinct * from cte;

See the demo.
Results:

| id | parentid | managerid | status |
| ---- | -------- | --------- | ------ |
| 3741 | | 1709 | 7 |
| 3742 | 3741 | 1709 | 12 |
| 3749 | 3742 | 1709 | 12 |

• forpas: Why? Check this: db-fiddle.com/f/rH56KyeyNTE3Azd2XTzCqh/2 It returns all rows because of status = 4.
• Jinal Tailor: If status !=12 then the result should be the same which is given description point no 2.
• forpas: What is the logic for this? Do you want it to be hardcoded?
• Jinal Tailor: You can refer working demo in https://demos.telerik.com/kendo-ui/treelist/index. Apply any filter using not equal to and check the result.

Answer 4

WITH RECURSIVE CTE AS
(
 SELECT *
 FROM tablename
 WHERE status = 12
 UNION
 SELECT t.*
 FROM tablename t
 INNER JOIN cte c ON c.Id = t.parentid
)
SELECT t.*
FROM tablename t
LEFT JOIN cte c on t.id=c.id
WHERE c.id IS NULL
ORDER BY id ASC NULLS FIRST;

• Not a bug: Hi Vijay, Welcome to SO :) While this answer may solve the original problem but it is always better to describe the answer how exactly it is solving the problem :)