How to return a key value ONLY if ALL the foreign keys in another table have a certain status (ORACLE)

How do I return the key (not a primary key): bwb_procesinstantie_id of the table wachtbox ONLY if ALL ‘foreign key’ values in the related table status have the status = “B”

Functionality: A process contains several rel_code. But the process can only be further processed if all the rel_codes have the right status…in this case ‘B’.

This is the table wachtbox. As you can see the bwb_procesinstantie_id is the same in all three records.

This is the table status

The distinct bwb_processinstantie_id value may only be returned if all the bgst_status fields have the value ‘B’

So in the above example. NO bwb_procesinstantie_id should be returned because one of the linked records has status ‘N’.

Answer

You could use:

SELECT t1.bwb_processinstantie_id
FROM t1
JOIN t2
  ON t1.bwb_rel_code = t2.bstg_rel_code
GROUP BY t1.bwb_processinstantie_id
HAVING COUNT(*) = COUNT(CASE WHEN t2.bgst_status = 'B' THEN 1 END)