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.
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