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.  
LVL 1
ttist25Asked:
Who is Participating?
 
jimyXCommented:
Select * from Table1 T1 where T1.field1 not in (select T2.field1 from Table2 T2) and T1.date ='20110323'
0
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.