Re: HELP: SQL SELECT

tkyte_at_us.oracle.com (Thomas J Kyte) wrote:
>"Beth J. Katcher" <katcher_at_a1.tch.harvard.edu> wrote:
> . . .
>>select card_no from pt_code
>>where diag_cod = '621.100'
>> and card_no in (select card_no from pt_code where diag_cod = '303.500')
>> and card_no in (select card_no from pt_code where diag_cod = '339.000');
>

 . .
>Or how about:
>
>select card_no from pt_cod
>where diag_cod in ( '621.100', '303.500', '339.000' )
>group by card_no
>having count(*) = 3
>/
> . . .

Lord knows I'd prefer something simpler than what I wrote, but I don't think that what you posted will do the trick. Of course it depends on the business rules, but as I do work in a hospital it was pretty easy for me to assume that this table contained something like diagnoses for patients for individual visits to a hospital or outpatient clinic. Thus each patient has an ID (card_no) and for each visit (identified by the date) there is a diagnosis. If there are multiple diagnoses for the visit, then multiple rows are written. Thus it would be possible for a patient to visit 3 times, each time with the same diagnosis of, for example, 621.100. In your query this patient would be retrieved. But I think that the original posted was looking for patients who had 3 separate diagnoses.

As I think about it, maybe throwing in a unique qualifier would help? This looks a little cleaner I think.

select card_no
from pt_code
where (card_no, diag_cod) in

    (select unique card_no, diag_cod
    from pt_code
    where diag_cod in ('621.100', '303.500', '339.000')) group by card_no
having count(*) = 3   

Beth Received on Tue Oct 17 1995 - 00:00:00 CET