CODEWITHSUNDEEP
sqloracle-database
Triodefreak
Triodefreak

Reputation: 45

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.

wachtbox

This is the table status

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

Upvotes: 1

Views: 83

Answers (2)

user5683823
user5683823

Reputation: 

select bwb_processinstantie_id
from   t1
where  bwb_processinstantie_id not in
         (
           select bwb_processinstantie_id
           from   t2
           where  lnnvl(bgst_status = 'B')
         )
;

This assumes that a key from the parent table must appear in the output, if it doesn't appear in the child table AT ALL. (This is the mathematical definition: given any condition whatsoever, even one that is clearly false, it is still true that all the elements of the empty set satisfy the condition - for the simple reason that there are NO elements that must satisfy the condition to begin with.)

Moreover, the solution assumes the id is primary key in the parent table, and it is constrained to not null in the second table. (Otherwise the subquery in the not in condition must add a where condition: that the ID be non-null.)

The solution allows for the "status" to be null - lnnvl(status = 'B') is true if status = 'B' is either false or unknown (which will be the case when the status is null).

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176024

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)

Upvotes: 1

Related Questions