SQL GENERAL: Select Records 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 field1,2,3. Table B has 2,000 records with field1,2,3. I would like to
- 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.
- I would like to join TableA to another Table called ISO_Country codes where I have stored country codes.
- 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
- Use a join to connect the two tables.
- Do not use select * as this will cause selection of fields from both tables specify the field you want in the select.
- 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 !='')