By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table T1( id primary key )
as
select 1 from dual union all
select 2 from dual union all
select 3 from dual union all
select 4 from dual union all
select 5 from dual union all
select 6 from dual union all
select 7 from dual
;
7 rows affected
CREATE TABLE t2(
T1_ID NUMBER NOT NULL
,Value1_ID NUMBER NOT NULL
,Value2_ID NUMBER
);
INSERT INTO t2 VALUES (1,1,2);
1 rows affected
INSERT INTO t2 VALUES (1,2,3);
1 rows affected
INSERT INTO t2 VALUES (1,3,1);
1 rows affected
INSERT INTO t2 VALUES (2,4,2);
1 rows affected
INSERT INTO t2 VALUES (2,5,2);
1 rows affected
INSERT INTO t2 VALUES (2,2,3);
1 rows affected
INSERT INTO t2 VALUES (2,6,4);
1 rows affected
INSERT INTO t2 VALUES (2,7,NULL);
1 rows affected
INSERT INTO t2 VALUES (3,8,2);
1 rows affected
INSERT INTO t2 VALUES (3,2,3);
1 rows affected
INSERT INTO t2 VALUES (4,9,2);
1 rows affected
INSERT INTO t2 VALUES (4,10,1);
1 rows affected
INSERT INTO t2 VALUES (5,2,2);
1 rows affected
INSERT INTO t2 VALUES (5,10,1);
1 rows affected
INSERT INTO t2 VALUES (6,4,3);
1 rows affected
INSERT INTO t2 VALUES (6,10,2);
1 rows affected
INSERT INTO t2 VALUES (7,07,2);
1 rows affected
CREATE TABLE t3(
ID NUMBER NOT NULL
,Value1 VARCHAR(9) NOT NULL
);
INSERT INTO t3(ID,Value1) VALUES (01,'Apples');
1 rows affected
INSERT INTO t3(ID,Value1) VALUES (02,'Cheese');
1 rows affected
INSERT INTO t3(ID,Value1) VALUES (03,'Cashews');
1 rows affected
INSERT INTO t3(ID,Value1) VALUES (04,'Bananas');
1 rows affected
INSERT INTO t3(ID,Value1) VALUES (05,'Cherries');
1 rows affected
INSERT INTO t3(ID,Value1) VALUES (06,'Skittles');
1 rows affected
INSERT INTO t3(ID,Value1) VALUES (07,'Chocolate');
1 rows affected
INSERT INTO t3(ID,Value1) VALUES (08,'Pears');
1 rows affected
INSERT INTO t3(ID,Value1) VALUES (09,'Kiwis');
1 rows affected
INSERT INTO t3(ID,Value1) VALUES (10,'Almonds');
1 rows affected
CREATE TABLE t4(
ID NUMBER NOT NULL
,Value2 VARCHAR(9) NOT NULL
);
INSERT INTO t4(ID,Value2) VALUES (1,'Nuts');
1 rows affected
INSERT INTO t4(ID,Value2) VALUES (2,'Fruit');
1 rows affected
INSERT INTO t4(ID,Value2) VALUES (3,'Cheese');
1 rows affected
INSERT INTO t4(ID,Value2) VALUES (4,'Candy');
1 rows affected
create table cat AS
SELECT 1 t3_id, 2 t4_id FROM DUAL
UNION ALL SELECT 4, 2 FROM DUAL
UNION ALL SELECT 5, 2 FROM DUAL
UNION ALL SELECT 8, 2 FROM DUAL
UNION ALL SELECT 9, 2 FROM DUAL
UNION ALL SELECT 2, 3 FROM DUAL
UNION ALL SELECT 3, 1 FROM DUAL
UNION ALL SELECT 10, 1 FROM DUAL
UNION ALL SELECT 6, 4 FROM DUAL
UNION ALL SELECT 7, NULL FROM DUAL
;
10 rows affected
SELECT t2.*
FROM t2
WHERE t2.Value2_ID IS NOT NULL AND NOT EXISTS (
SELECT 1 FROM cat WHERE cat.t3_id = t2.Value1_ID AND cat.t4_id = t2.Value2_ID
)
ORDER BY 1, 2, 3
| T1_ID | VALUE1_ID | VALUE2_ID |
|---|---|---|
| 5 | 2 | 2 |
| 6 | 4 | 3 |
| 6 | 10 | 2 |
| 7 | 7 | 2 |