Nested Query Help

[DaveP]

HI,

I'm new to nested queries and having trouble.
I'm trying to identify all members that have BOTH an 'A'
and a ('D'OR 'P') for a particular Drug

Table Name: Claims

Fields
Member Status Drug
John A Aspirin
John D Aspirin
Steve P Celebrex
Steve A Luvox
Sarah A Fosamax
Sarah P Fosamax

So in this example, I want it to return - John Aspirin
AND Sarah Fosamax- since they have BOTh and A and D (or
P) for the same drug.

 

[Allen Browne]

Use a subquery in the WHERE clause to get the combination.

Something like this:

SELECT Claims.*
FROM Claims
WHERE Status = 'A' AND EXISTS
( SELECT Member FROM Claims AS Dupe
WHERE Dupe.Member = Claims.Member
AND Dupe.Status IN ('D', 'P') );

If subqueries are new, here is Microsoft's introduction:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Davep]

Thanks. Unfortunately no results were returned.
I know there should be as I do this a longer way
(requiring 3 queries)...hmmm

 

[Guest]

Hi Dave,

I think that Allen accidentally left out a correlation of the drug name in
the subquery, but I think that should have given you too many results so I am
wondering if there is a syntax error in your query.

Try revising Allen's sql to:

SELECT Claims.*
FROM Claims
WHERE Status = 'A' AND EXISTS (SELECT Member FROM Claims AS Dupe WHERE
Dupe.Member = Claims.Member AND Dupe.Drug = Claims.Drug AND Dupe.Status IN
('D', 'P') );

If it still doesn't work, post back with your actaul query sql to see if we
can spot anything.

-Ted Allen