Home » SQL & PL/SQL » SQL & PL/SQL » Exclusive Select By Historical Data
Exclusive Select By Historical Data [message #256223] Fri, 03 August 2007 08:07 Go to next message
deay
Messages: 51
Registered: August 2005
Member
Hi Everyone

I need some help with selecting records, based on historical information,that excludes donors who don't have the same acctno throughout their history. Some donors can have 10,20,50 payment records.

SQL> select distinct acctno from payment where acctno is not null;

ACCTNO
-----------------
HOUSE
INTERNET
RADIO

3 rows selected.


example:

donorID   appealcode  acctno
1001       DRA0702    RADIO
1001       DRB0703    RADIO
1001       DRC0704    RADIO

donorID   appealcode  acctno
1002       DRA0702    RADIO
1002       DNE0703    INTERNET
1002       DCH0704    HOUSE


when I run my script:

select donorid from payment where acctno not in('INTERNET','HOUSE');


it includes donor 1002 since they do have a 'RADIO' record. I need that donor excluded from the select since "historically" their acctno is not exclusively RADIO.

thanks

Re: Exclusive Select By Historical Data [message #256227 is a reply to message #256223] Fri, 03 August 2007 08:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can use MINUS to remove the extra records:
with tbl as (select 1001  donorid,      'DRA0702' appealcode,   'RADIO' acctno from dual union all
             select 1001      ,'DRB0703',    'RADIO' from dual union all
             select 1001       ,'DRC0704'    ,'RADIO' from dual union all
             select 1002       ,'DRA0702'    ,'RADIO' from dual union all
             select 1002       ,'DNE0703'    ,'INTERNET' from dual union all
             select 1002       ,'DCH0704'    ,'HOUSE' from dual)
select donorid from tbl where acctno not in('INTERNET','HOUSE')
minus
select donorid from tbl where acctno in('INTERNET','HOUSE');
Re: Exclusive Select By Historical Data [message #256248 is a reply to message #256227] Fri, 03 August 2007 10:07 Go to previous message
deay
Messages: 51
Registered: August 2005
Member
thanks JRowBottom...didn't think to look into using the Set Operators.
Previous Topic: Outher Join Problem
Next Topic: Query Execution in UNIX AIX system
Goto Forum:
  


Current Time: Thu Dec 08 16:29:08 CST 2016

Total time taken to generate the page: 0.09291 seconds