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

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.
0
jasonsbytes
Asked:
jasonsbytes
2 Solutions
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

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.

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