SQL logic to fail a check if any of the related customers has failed

I have the requirement to flag the customers Y only when all the related customers have also passed the check. below are the two tables:

relationship table :

customer_id related_customer
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
11 11
11 22
22 11
22 22

Check table

customer_id check_flag
1 y
2 y
3 n
11 y
22 y

I want output like below:

customer_id  paas_fail_flag
1 n
2 n
3 n
11 y
22 y

output justification: since 1,2,3 are related customers and since one of them (3) has n in table 2 , so all the related customers should also have n. 11,22 are related customers and both have y in table 2.so in output both should have y.

Answer

You need to join relationship to check and use conditional aggregation:

SELECT r.customer_id,
       COALESCE(MAX(CASE WHEN c.check_flag = 'n' THEN c.check_flag END), 'y') paas_fail_flag  
FROM relationship r INNER JOIN "check" c 
ON c.customer_id = r.related_customer
GROUP BY r.customer_id
ORDER BY r.customer_id

See the demo.