sqloraclejoinselectexists

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


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.


Solution

  • 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>