interesting query problem, how to resolve? Analytic functions?

All,

I have a query problem that might lead to an interesting solution.

Assume three tables, Claims, Members, Drugs.

Claims contains medical claims, Members contains Members, Drugs contains Drugs.

One member to many claims, one claim to one drug.

What I need to do is write a query that lets me remove an ENTIRE set of member records from the resultset if ONE or more claims were for certain drugs.

In other words, if a member went to the doctor (had a claim) five times, and the doctor prescribed drug x just once, I don't want to see ANY of the records for that member.

The current way the query is written is this:

select *
from claims, members
where claims.member_id not in
 (select member_id
 from claims
 where drug_gid in
  (select drug_gid
   from drugs
   where drug like 'SOMEDRUG'));

which works, but slowly, since this is a warehouse and it requires multiple visits to the large claims fact table, and the member table isn't small either.

It occurs to me that I might be able to use an analytic function for this - each partition is a set of member, sort by nulls on top, and if the first record has null for a given field, exclude the set. Except I don't know how to do this.

Any solutions, input, advice, or twenty dollar bills welcome.

Thanks,

Jack

warehouse dba in Texas Received on Tue May 27 2003 - 16:50:04 CDT