By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table T2 ( id, value1_id, value2_id ) as
select 1, '01', 2 from dual union all
select 1, '02', 3 from dual union all
select 1, '03', 1 from dual union all
select 2, '04', 2 from dual union all
select 2, '05', 2 from dual union all
select 2, '02', 3 from dual union all
select 2, '06', 4 from dual union all
select 2, '07', null from dual union all
select 3, '08', 2 from dual union all
select 3, '02', 3 from dual union all
select 4, '09', 2 from dual union all
select 4, '10', 1 from dual union all
select 5, '02', 2 from dual union all
select 5, '10', 1 from dual union all
select 6, '04', 3 from dual union all
select 6, '10', 2 from dual union all
select 7, '07', 2 from dual;
17 rows affected
WITH mappings ( name, category ) AS (
SELECT '01', 2 FROM DUAL UNION ALL
SELECT '02', 3 FROM DUAL UNION ALL
SELECT '03', 1 FROM DUAL UNION ALL
SELECT '04', 2 FROM DUAL UNION ALL
SELECT '05', 2 FROM DUAL UNION ALL
SELECT '06', 4 FROM DUAL UNION ALL
SELECT '07', NULL FROM DUAL UNION ALL
SELECT '08', 2 FROM DUAL UNION ALL
SELECT '09', 2 FROM DUAL UNION ALL
SELECT '10', 1 FROM DUAL
)
SELECT *
FROM T2 t
WHERE NOT EXISTS (
SELECT 1
FROM mappings m
WHERE t.value1_id = m.name
AND ( t.value2_id = m.category
OR ( t.value2_id IS NULL AND m.category IS NULL ) )
);
| ID | VALUE1_ID | VALUE2_ID |
|---|---|---|
| 5 | 02 | 2 |
| 6 | 04 | 3 |
| 6 | 10 | 2 |
| 7 | 07 | 2 |