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
    )
`

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     |

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;