By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE "TEST1"
( "DEPARTMENT" NUMBER(3,0),
"VALUE1" VARCHAR2(26 BYTE),
"VALUE2" VARCHAR2(26 BYTE),
"CHECK_CONDITION1" NUMBER(3,0),
"CHECK_CONDITION2" NUMBER(3,0)
) ;
CREATE TABLE "TEST3"
( "DEPARTMENT" NUMBER(3,0),
"VALUE1" VARCHAR2(26 BYTE),
"VALUE2" VARCHAR2(26 BYTE),
"VALUE3" VARCHAR2(26 BYTE)
);
BEGIN
Insert into TEST1 (DEPARTMENT,VALUE1,VALUE2,CHECK_CONDITION1,CHECK_CONDITION2) values (1,'A','Z',1,0);
Insert into TEST1 (DEPARTMENT,VALUE1,VALUE2,CHECK_CONDITION1,CHECK_CONDITION2) values (1,'B','Y',1,0);
Insert into TEST3 (DEPARTMENT,VALUE1,VALUE2,VALUE3) values (1,'A','T','Whatever');
Insert into TEST3 (DEPARTMENT,VALUE1,VALUE2,VALUE3) values (1,'Z','Y','Whatever');
Insert into TEST3 (DEPARTMENT,VALUE1,VALUE2,VALUE3) values (1,'B','Y','Whatever');
END;
/
1 rows affected
select distinct t3.*
from test3 t3
INNER JOIN test1 t1
ON ( t3.department=t1.department )
WHERE t3.value1 not in (
select value1
from test1
where department=t3.DEPARTMENT
AND t1.CHECK_CONDITION1 = 1
)
| DEPARTMENT | VALUE1 | VALUE2 | VALUE3 |
|---|---|---|---|
| 1 | Z | Y | Whatever |
update test1 set check_condition1 = 0;
2 rows affected
select distinct t3.*
from test3 t3
INNER JOIN test1 t1
ON ( t3.department=t1.department )
WHERE t3.value1 not in (
select value1
from test1
where department=t3.DEPARTMENT
AND t1.CHECK_CONDITION1 = 1
)
| DEPARTMENT | VALUE1 | VALUE2 | VALUE3 |
|---|---|---|---|
| 1 | B | Y | Whatever |
| 1 | A | T | Whatever |
| 1 | Z | Y | Whatever |