Re: HELP: SQL SELECT

"Beth J. Katcher" <katcher_at_a1.tch.harvard.edu> wrote:

>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

Of course you are right, if the same CARD_NO, DIAG_COD pair can exist you must rewrite my query as:

select card_no from pt_cod
where diag_cod in ( '621.100', '303.500', '339.000' ) group by card_no

having count( distinct diag_cod ) = 3
              ^^^^^^^^^^^^^^^^^ /* instead of just count(*) */

Sorry, didn't think about it that way.

Your query:

>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
>

however suffers from my same problem. If the database has:

CARD_NO     DIAG_COD
-------     ---------
1           621.100
1           621.100
1           621.100

in it, then the subquery would return only ( 1, 621.100 ) but the outer query would count all three rows again.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Thu Oct 19 1995 - 00:00:00 CET