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 ;
3 rows affected
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 ;
12 rows affected
select t2.*, 'T1 id not valid' as status
from t2
where t2.id not in ( select id from T1 )
union all
select t2.*, 'value1_id <-> value2_id mapping incorrect '
from t1 join t2 on t1.id = t2.id
where
( t2.value1_id in ('01','04','05','08','09') and t2.value2_id <> 2 )
or
( t2.value1_id = '02' and t2.value2_id <> 3 )
or
( t2.value1_id in ('03','10') and t2.value2_id <> 1 )
or
( t2.value1_id = '06' and t2.value2_id <> 4 )
or
( t2.value1_id = '07' and t2.value2_id is null )
;
| ID | VALUE1_ID | VALUE2_ID | STATUS |
|---|---|---|---|
| 4 | 09 | 2 | T1 id not valid |
| 4 | 10 | 1 | T1 id not valid |
| 2 | 07 | null | value1_id <-> value2_id mapping incorrect |
create table T3 ( id, value1 unique )
as
select '01', 'Apples' from dual union all
select '02', 'Cheese' from dual union all
select '03', 'Cashews' from dual union all
select '04', 'Bananas' from dual union all
select '05', 'Cherries' from dual union all
select '06', 'Skittles' from dual union all
select '07', 'Chocolate' from dual union all
select '08', 'Pears' from dual union all
select '09', 'Kiwis' from dual union all
select '10', 'Almonds' from dual;
10 rows affected
create table T4 ( id, value2 unique )
as
select 1, 'Nuts' from dual union all
select 2, 'Fruit' from dual union all
select 3, 'Cheese' from dual union all
select 4, 'Candy' from dual ;
4 rows affected
create table map( category, product )
as
select 'Fruit', 'Apples' from dual union all
select 'Cheese', 'Cheese' from dual union all
select 'Nuts', 'Cashews' from dual union all
select 'Fruit', 'Bananas' from dual union all
select 'Fruit', 'Cherries' from dual union all
select 'Candy', 'Skittles' from dual union all
select 'Candy', 'Chocolate' from dual union all
select 'Fruit', 'Pears' from dual union all
select 'Fruit', 'Kiwis' from dual union all
select 'Nuts', 'Almonds' from dual;
10 rows affected
alter table map
add (
constraint m_pk primary key ( category, product )
, constraint m_category_fk foreign key ( category ) references T4 ( value2 )
, constraint m_product_fk foreign key ( product ) references T3 ( value1 )
) ;
select T2.id, T2.value1_id, T2.value2_id
from T2
minus (
select T2.id, T2.value1_id, T2.value2_id
from T2
join (
--
select T4.id categoryid, T3.id productid, M.category, M.product
from T4
join map M on T4.value2 = M.category
join T3 on T3.value1 = M.product
--
) C -- correct mappings
on
C.productid = T2.value1_id
and C.categoryid = T2.value2_id
) ;
| ID | VALUE1_ID | VALUE2_ID |
|---|---|---|
| 2 | 07 | null |