How to find a combination of two columns that is NOT in the other table in ORACLE SQL?

Question

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.

Answer 1

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>

Answer 2

A set operation like MINUS may be costly on large data sets, in that case you may try:

select
    id, org, dsit
from (
    select id from maint
    group by id having(count(*) < (select count(*) from reft))
) g
cross join reft r
where not exists(
    select 1 from maint m where r.org = m.org and r.dsit = m.dsit
        and g.id = m.id
)
;