find all records in table a not in table b

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

find all records in table a not in table b

Author

Topic

wldodds
Starting Member

20 Posts

Posted - 2012-09-05 : 21:44:31

I have table a which contains about 10 columns and table b contains the same 10 columns, however in this case only the first 2 columns are primary keys so I need to find all records in table b that don't exist in table a.

Here is a sample of what I'm trying to do:

select CUSTNMBR, ADRSCODE, SLPRSNID, UPSZONE, SHIPMTHD, TAXSCHID, CNTCPRSN, ADDRESS1, ADDRESS2, ADDRESS3, COUNTRY, CITY, STATE, ZIP, PHONE1, PHONE2, PHONE3, FAX, MODIFDT, CREATDDT, GPSFOINTEGRATIONID, INTEGRATIONSOURCE, INTEGRATIONID, CCode, DECLID, LOCNCODE, SALSTERR,
USERDEF1, USERDEF2, GETDATE()
from aa_temp..rm00102
where custnmbr not in (select custnmbr from aa..RM00102)
and adrscode not in (select adrscode from aa..rm00102)

basically I want to show all records from aa_temp..rm00102 that are not in aa..rm00102 joined on custnmbr and adrscode but not exactly sure how to do that.

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-05 : 21:47:58

[code]
select CUSTNMBR, ADRSCODE, SLPRSNID, UPSZONE, SHIPMTHD, TAXSCHID, CNTCPRSN, ADDRESS1, ADDRESS2, ADDRESS3, COUNTRY, CITY, STATE, ZIP, PHONE1, PHONE2, PHONE3, FAX,
MODIFDT, CREATDDT, GPSFOINTEGRATIONID, INTEGRATIONSOURCE, INTEGRATIONID, CCode, DECLID, LOCNCODE, SALSTERR,
USERDEF1, USERDEF2, GETDATE()
from aa_temp..rm00102 t
where not exists (select 1 from aa..RM00102 where custnmbr = t.custnmbr and adrscode = t.adrscode)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wldodds
Starting Member

20 Posts

Posted - 2012-09-05 : 21:55:38

Thank you very much works perfectly!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-05 : 22:07:39

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources