• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 707
  • Last Modified:

Select records in table1 where table1.field1 doesn't exist in table2.field1

Hey there,

I've got two tables, table1 and table2.  Both tables have identical fields except that table1 has an added date field named date (datetime).

I'd like to find all of the records in table1 where table1.field1 is not in (doesn't exist in) table2.field1.

Not sure if this is helpful but, field1 contains duplicate values in both tables.

I'd like to further filter my results by selecting only records from table1 where date='20110323'.

Thanks for any help you can give.  
0
ttist25
Asked:
ttist25
2 Solutions
 
Ephraim WangoyaCommented:

select *
from table2
where not exists (select 1
                             from table2
                            where table2.field1 = table1.field1)
and date='20110323'
0
 
jimyXCommented:
Select * from Table1 T1 where T1.field1 not in (select T2.field1 from Table2 T2) where date ='20110323'
0
 
jimyXCommented:
Select * from Table1 T1 where T1.field1 not in (select T2.field1 from Table2 T2) and T1.date ='20110323'
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
lcohanDatabase AnalystCommented:
select * from table1
where field1 in
(select field1 from table1
      except
select field1 from table2)
0
 
morguloCommented:
Table2.field1 has to be not null. If Table2.field1 is nullable you should use:
Select * from Table1 T1 where T1.field1 not in (select T2.field1 from Table2 T2 where field1 is not null) and T1.date ='20110323'
0
 
ttist25Author Commented:
Thanks everyone.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now