[SOLVED] SQL to find record with a value in one table but not another table

SQL to find record with a value in one table but not another table

I have two tables: Address and Address_Backup. Each table can contain multiple rows of data for a single individual because there are multiple address types stored in this table. So the data looks something like this:

ID    Code   Description  Description2  City   State
6798  HOME   478 Elm      NULL          Boise  ID
6798  OTHER  405 S Main   NULL          NULL   NULL

Address_Backup is supposed to be identical to Address, but we've found some data that exists in Address_Backup that doesn't exist in Address. I need a query that joins the ID numbers in the two tables and returns data for addresses where the "Other" code type exists in the Address_Backup table but does not exist in the Address table.

Solution

Assuming your IDs do not change in either table so that ID 100 in Address always points to ID 100 in Address_Backup:

  SELECT * 
    FROM Address_Backup
     WHERE ID NOT IN (SELECT ID FROM Address) AND Code = 'OTHER'

using NOT EXISTS

 SELECT * 
  FROM Address_Backup AS B
  WHERE NOT EXISTS (SELECT 1 FROM Address WHERE ID = B.ID) AND Code = 'OTHER'