Simple Oracle SQL query help needed

I need all id's in table1 that are not in table2 after a specific date.

table1:
id

table2:
date
id

I need to know all of the id's in table 1 that do not appear in table2 with a date greater than 1/1/07 for example.

This is an Oracle db.
LVL 17
jasonsbytesAsked:
Who is Participating?
 
sathyagiriCommented:
Another way
select id from table1 where not exists (select 1 from table2 where table2.id = table1.id and table2.date >  to_date('1/1/07','MM/DD/YY'))
0
 
mbizupCommented:
SELECT Table1.ID
FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
WHERE Table2.ID IS NULL AND Table1.Date > [SomeDate]
0
 
slightwv (䄆 Netminder) Commented:
Another way ( not sure which is faster ):
---------------------------------------
select id from table 2 where date > to_date('1/1/07','MM/DD/YY')
minus
select id from table1
/
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.

 
jasonsbytesAuthor Commented:
Here were my results:
mbizup: no results returned
slightwv:  seems to return opposite of what I want.  I probably could just reverse it and get what I want.
sathyagiri:  this seems to work.

Thank you...
0
 
slightwv (䄆 Netminder) Commented:
>>seems to return opposite of what I want.

oops.  Yes, just reverse the minus and it should work.

You should run an explainn plan on both to see which works best in your database.   My money is on the 'minus' query.
0
 
jasonsbytesAuthor Commented:
Thanks I will...  This is just a one time (manual) data extract though, so not a big deal really.
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.