Oracle: Filtering results based on return of past query

[pviren]

Greetings!

We have a Users table with over 3 million user records in it (like a data warehouse). The requirement is to execute 2 queries on the Users table, where the return of the second query should not have any users that were returned in the first query.

For e.g. Consider a sweepstake user-list, where you want to send flyers to people, notifying them about prizes, but do not want to send two flyers to the same person, even if he fulfills the selection criteria for both the sweepstakes.

So -

Query.1. has conditions, which state: All Users from New York, with age=25 (independently run, returns 500,000 users)
Query.2. has conditions, which state: All Users of age=25 (independently run, returns 700,000 users)

Expected Output:
Query 1 should return 500,000
Query 2 should return 700,000 MINUS those users that were already returned in Query 1. i.e. Only return all users of age 25, but who are not from New York

Any inputs will be much appreciated!

Thanks,

[homerrocks]

Well, not sure if this helps, but based on your example could your second query exclude the users from NY with a where clause like "select columns from users where age = 25 and homestate !=NY"?

[rt_roh]

if ur queries are fixed then u can use the earlier solution.

If they can vary u can use minus itself..
i.e. for 2nd qry write 2nd query minus 1st query. This is assuming result sets of both queries are identical column wise.

select * _ __ _ _ _ _
minus
select * _ _ _ _ _


Also 2nd qry can be written as follows if u can identify unique identification columns in both the queries

select * from _ _ _ _ _
where <where clause of 2nd qry>
and (unique key) not in (select unique key of first qry)

this solution is going to be typical/customized as per ur fields/qry.