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?

Improve company productivity with a Business Account.Sign Up

x
 
jimyXConnect With a Mentor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
lcohanDatabase AnalystCommented:
select * from table1
where field1 in
(select field1 from table1
      except
select field1 from table2)
0
 
morguloConnect With a Mentor Commented:
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.