Solved

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

Posted on 2011-03-24
6
652 Views
Last Modified: 2012-05-11
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
Comment
Question by:ttist25
6 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 35210654

select *
from table2
where not exists (select 1
                             from table2
                            where table2.field1 = table1.field1)
and date='20110323'
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35210666
Select * from Table1 T1 where T1.field1 not in (select T2.field1 from Table2 T2) where date ='20110323'
0
 
LVL 24

Accepted Solution

by:
jimyX earned 250 total points
ID: 35210680
Select * from Table1 T1 where T1.field1 not in (select T2.field1 from Table2 T2) and T1.date ='20110323'
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 39

Expert Comment

by:lcohan
ID: 35210693
select * from table1
where field1 in
(select field1 from table1
      except
select field1 from table2)
0
 
LVL 5

Assisted Solution

by:morgulo
morgulo earned 250 total points
ID: 35210723
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
 
LVL 1

Author Closing Comment

by:ttist25
ID: 35211159
Thanks everyone.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now