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

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 !=”)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s