Re: How to do this in Mckoi

M. A. Sridhar wrote:
> --- Chas Douglass <[email protected]> wrote:
> 
> 
>>I'm an SQL neophyte and struggling a bit.
>>
>> From my studying it looks like I want a "scalar subquery" which Mckoi 
>>doesn't provide.  Is there another way to do this?
>>
>>A slightly contrived example:
>>
>>Say I have tables Patient, Diseases, and Medications.  Diseases and 
>>Medications are weak entities related to Patients.
>>
>>I want to select all patients that have disease x and for each of those 
>>patients show the count of medications they have taken.
>>
>>What I think I want is:
>>
>>SELECT
>>   Patient.name,
>>   Patient.birthDate,
>>   (SELECT COUNT(*)
>>      FROM Medications
>>      WHERE Patient.id = Medications.patientId)
>>      AS num_medications
>>FROM Patient, Diseases
>>WHERE Patient.id = Diseases.patientId AND
>>    Diseases.disease = 'x';
>>
>>But this doesn't work in Mckoi because of the subquery.
>>
>>Thanks for any help.
>>
>>Chas Douglass
>>
> 
>  
> In your example, this might work (assuming that med_id is the primary key for
> the medications table):
> 
> SELECT
>    Patient.name,
>    Patient.birthDate,
>    count (distinct medications.med_id)
>    from patient, medications, diseases
>    where patient.id = medications.patientId 
>    and Patient.id = Diseases.patientId  and Diseases.disease = 'x'
>    group by patient.name, patient.birthDate;
> 
> This will only show patients that have taken at least one medication. If you
> want to include patients who have taken no medication but have disease 'x',
> you will probably need to use scalar subqueries, something like this:
> 
> SELECT
>    Patient.name,
>    Patient.birthDate,
>    meds.medCount
>    from patient
>    left join (select medications.patientId as patId,
> count(medications.med_id) as medCount
>               from medications
>               group by medications.patientId) as meds on meds.patId =
> Patient.id
>    , diseases
>    where  Patient.id = Diseases.patientId  and Diseases.disease = 'x';
> 
> 
> 
> Take a look at http://mckoi.com/database/mail/subject.jsp?id=6468.
> 
> Hope this helps.
> 
> 

Thanks, it does.

Maybe I should just make a VIEW of the "Patient LEFT JOIN (SELECT ..." 
and then I could pick out the number of meds for a patient quite easily.

Isn't it true that you can only have one LEFT JOIN per query?  That 
would be another reason to use the VIEW.

Chas Douglass




---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to [email protected]