Share via

SQL Server: Select Records From One Table Based on Fields in Another Table

  • Article

Introduction

In SQL one can use the “NOT IN” or “EXISTS” to select records in a table based on the existence of field values of that table in another table. This works well when you have just one field as a criteria but when you have multiple fields it becomes more complex and it ignores the records where those fields are blank or null or no value. This article will show a query that you can use to get around that.

Case Overview

Table A has 10,000 records with field 1,2,3. Table B has 2,000 records with field 1,2,3. I would like to

  1. Select records from TableA where TableA.field1 does not exist in TableB.field1 or TableA.field2 does not exist in TableB.field2 or TableA.field3 does not exist in TableB.field3.
  2. I would like to join TableA to another Table called ISO_Country codes where I have stored country codes.
  3. I would also like all records from TableA where fields 1,2,3 are blank and do not meet the condition of (1)

Solution design

  1. Use a join to connect the two tables.
  2. Do not use select * as this will cause selection of fields from both tables specify the field you want in the select.
  3. Use INTERSECT command to find the items common to the queries, this will include the blanks fields.

Query Code

With b as (select field1,field2, field3,field4,field5,field6,field7  
  
 from dbo.TableA where field5 = 'test')  
  
 Select field1,field2, field3,field4,field5,field6,field7  
  
from b 
  
left join ISO_countrycodes 
  
on b.field6=ISO_countrycodes.TwoNme where b.field1 not in (select field1 from TableB where field1 !='') 
  
intersect
  
Select field1,field2, field3,field4,field5,field6,field7  
  
from b 
  
left join ISO_countrycodes 
  
on b.field6=ISO_countrycodes.TwoNme where b.field2 not in (select field2 from TableB where field2 !='') 
  
intersect
  
Select field1,field2, field3,field4,field5,field6,field7  
  
from b 
  
left join ISO_countrycodes 
  
on b.field6=ISO_countrycodes.TwoNme where b.field3 not in (select field3 from TableB where field3 !='')