Oracle compare results from query against a table

I need to compare results from a query against a table. I have the following query:

select
i.person_id,
a.APPELLANT_FIRST_NAME,
a.APPELLANT_MIDDLE_NAME,
a.APPELLANT_LAST_NAME,
a.databaseidnumber,
a.CTAPPEALSNUMBER,
a.NOTICEOFAPPEALFILEDDATE,
a.RECORDDUEDATE,
a.PETITIONONAPPEALDUEDATE,
a.PETITIONONAPPEALFILEDDATE,
a.RESPONSETOPETITIONDUEDATE,
a.RESPONSETOPETITIONFILEDDATE,
a.CERTFILEDDATE,
a.MANDATEISSUEDDATE

from CWLEGAL.individuals i inner join CWLEGAL.tblappealsdatarevisionone a
on a.d_n_number1 = i.casenm  and  a.appellant_first_name = i.first_name  and  a.appellant_last_name = i.last_name
order by databaseidnumber;

Now I need to see what databaseidnumber’s from Table A don’t appear in the results from the query above.

Answer

You can use left join and check for null:

select a.*
from CWLEGAL.tblappealsdatarevisionone a left join
     CWLEGAL.individuals i 
     on a.d_n_number1 = i.casenm and
        a.appellant_first_name = i.first_name and
        a.appellant_last_name = i.last_name
where i.casenm is null

Advertisement

Answer