Skip to content
Advertisement

get records that doesnt exists in another table

I have a temp table over a 1000 rows but for example purposes as follows

#Table

ID    |  Code
33641 | 421230
33642 | 421230
33623 | 200585489
33623 | 45109

and then i have another table as follows (this is not a temp table) Compliance Table

ID    |  Code
33623 | 421230
33627 | 421230
33637 | 421230
33641 | 200585489
33642 | 200585489
33623 | 45109

how do i check if the records from the temp table doesnt exists in the Compliance table per its ID and Code

so from the above data i would want the following out put

Items that dont match: output table

Id    | Code
33641 | 421230  //doesnt exists
33642 | 421230  //doesnt exists
33623 | 200585489  //doesnt exists

from the 4 records in the #temp table 3 of the above records dont exists in the compliance table

Advertisement

Answer

Just use not exists:

select t.*
from #table t
where not exists (select 1
                  from compliance c
                  where c.id = t.id and c.code = t.code
                 );
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement