I have a main table that contains the data I have, and a reference table telling me what values the main table should have. I want to check if the main table contains all required values, and if not, return the missing values. My main table look like the following:
ID ORG DSIT
-----------
1 A AA
1 B BB
2 A AA
2 B BB
2 C CC
and my reference table looks like:
ORG DSIT
---------
A AA
B BB
C CC
That means for each ID in main table, it should have 3 columns with (ORG, DSIT) = (A,AA), (B,BB) and (C,CC) base on the reference table. Thus, here ID 2 is ok, but ID 1 is missing a (C,CC) combination for ORG and DSIT
I have try left join, by doing so I can get that (C,CC) is missing but cannot tell which ID it belongs to. I also try NOT EXIST but it just not work in this way.
I am expect to see the result:
ID ORG DSIT
-----------
1 C CC
OR
ID MESSAGE
-----------
1 'C and CC is missing'
Thanks in advance; and let me know if I can explain it better.
Here's one option.
Sample data:
SQL> with
2 maint (id, org, dsit) as
3 (select 1, 'A', 'AA' from dual union all
4 select 1, 'B', 'BB' from dual union all
5 select 2, 'A', 'AA' from dual union all
6 select 2, 'B', 'BB' from dual union all
7 select 2, 'C', 'CC' from dual
8 ),
9 reft (org, dsit) as
10 (select 'A', 'AA' from dual union all
11 select 'B', 'BB' from dual union all
12 select 'C', 'CC' from dual
13 )
Query begins here; it uses the minus set operator which returns the difference between two sets: one is all values you expect, and one is all values you have. Difference is what you're missing.
14 -- all values you expect
15 select b.id, r.org, r.dsit
16 from (select distinct m.id from maint m) b cross join reft r
17 minus
18 -- all values you have
19 select a.id, a.org, a.dsit
20 from maint a;
ID ORG DSIT
---------- --- ----
1 C CC
SQL>