Using SQL recursive query as AND statement

Question

I have the following flowchart. Hopefully, it's self-explanatory

On top of the hierarchy there's a request that is a basic parent of all the request below it. Requests below have the 'id', 'parent_id', 'state' fields

My final goal is to find all parents ids that satisfy all AND statements including the last one (hierarchical query). However, I don't know how to use it as an AND statement.

The hierarchical query looks like this:

with cte 
    as (select id, state
        from tbl_request as rH 
        WHERE id = /* each id from the very first select */
        UNION ALL
        select rH.id, rH.state
        from tbl_request as rH 
        join cte
          on rH.parent_id = cte.id
             and (cte.state is null or cte.state NOT IN('not-legit'))
       )
    select case when exists(select 1 from cte where  cte.state IN('not-legit'))
        then 1 else 0 end

Expectantly, it does what it's supposed to

The solution was suggested in the question

Return true/false in recursive SQL query based on condition

For your convenience, here's a SQL Fiddle

Answer 1

I think I've worked out what you want.

You need to recurse through all the nodes and their children, returning its state and its ultimate root parent_id.

Then aggregate by that ID and exclude any group that contains a row with state = 'not-legit'. In other words, flip the logic to a double negative.

WITH cte AS (
    SELECT rH.id, rH.state, rH.id AS top_parent
        FROM tbl_request as rH 
        WHERE (rH.state is null or rH.state <> 'not-legit')
          AND rH.parent_id IS NULL
    UNION ALL
    SELECT rH.id, rH.state, cte.top_parent
        FROM tbl_request as rH
        JOIN cte
          ON rH.parent_id = cte.id
)
SELECT top_parent
FROM cte
GROUP BY
    cte.top_parent
    HAVING COUNT(CASE WHEN cte.state = 'not-legit' THEN 1 END) = 0;

You could also change the logic back to a positive, but it would need to look like this:
HAVING COUNT(CASE WHEN cte.state is null or cte.state <> 'not-legit' THEN 1 END) = COUNT(*)
In other words, there are the same number of these filtered rows as there are all rows.
This feels more complex than what I have put above.

SQL Fiddle