Re: How to do this in Mckoi

Yes, I got that example to work.  I think I messed up translating 
Kevin's original when I tried to do the LEFT JOIN.

This makes sense to me, thanks to you and Kevin, I appreciate the help.

But this brings up another question -- if I use multiple LEFT JOIN's, 
won't that mess up the COUNT?

Suppose I want to pull in another aggregate from another table.  Won't 
the LEFT JOINs "multiply" out?

Thanks

Chas Douglass

Tobias Downer wrote:
> Using Kevin's example, the following query should do it;
> 
>    SELECT Patient.name, Patient.birthDate, count(Medications.name)
>      FROM Patient LEFT JOIN Medications
>                     ON (Patient.id = Medications.patientId)
>                 JOIN Diseases
>                     ON (Patient.id = Diseases.patientId)
>     WHERE Diseases.disease = 'Memory Loss'
>  GROUP BY Patient.id
> 
> This results in;
> 
> +-------+------------+-------------------------+
> | name  | birthDate  | count(Medications.name) |
> +-------+------------+-------------------------+
> | Sam   | 1965-02-02 | 1                       |
> | Sally | 1960-03-03 | 0                       |
> +-------+------------+-------------------------+
> 
> Note that this lists Sally even though she is taking no medications.  A 
> useful feature of the aggregate 'COUNT(field_name)' is that it only 
> counts non-NULL entries so works well with a LEFT JOIN.
> 
> Toby.
> 
> Kevin Schmidt wrote:
> 
>> The tables for my test are:
>>
>> Patient:
>> id, name, birthDate
>> 1, Joe, 1970-01-01
>> 2, Sam, 1965-02-02
>> 3, Sally, 1960-03-03
>>
>> Medications:
>> id, patientId, name
>> 1, 1, Zyrtec
>> 2, 1, Viagra
>> 3, 2, Viagra
>>
>> Diseases:
>> id, patientId, disease
>> 1, 1, Allergies
>> 2, 1, ED
>> 3, 2, ED
>> 4, 2, Memory Loss
>> 5, 3, Memory Loss
>>
>> The results from this query:
>>
>> SELECT Patient.name, Patient.birthDate, count(*) AS num_medications
>> FROM Patient join Medications on Patient.id = Medications.patientId 
>> join Diseases on Patient.id = Diseases.patientId
>> WHERE Diseases.disease = 'ED'
>> GROUP BY Patient.name, Patient.birthDate
>>
>> are:
>>
>> name, birthDate, num_medications
>> Joe, 1970-01-01, 2
>> Sam, 1965-02-02, 1
>>
>> which seems correct and what you wanted as it is listing Joe and Sam 
>> as they have ED and then lists 2 medications for Joe and 1 for Sam.
>>
>> Kevin
>>
>> Chas Douglass wrote:
>>
>>> Kevin Schmidt wrote:
>>>
>>>> Why won't a query that simply just joins the three tables work?  
>>>> Something like:
>>>>
>>>> SELECT Patient.name, Patient.birthDate, count(*) AS num_medications
>>>> FROM Patient join Medications on Patient.id = Medications.patientId 
>>>> join Diseases on Patient.id = Diseases.patientId
>>>> WHERE Diseases.disease = 'x'
>>>> GROUP BY Patient.name, Patient.birthDate
>>>>
>>>> Kevin
>>>>
>>>
>>> Thanks so much for your help.
>>>
>>> I tried this and got no results.
>>>
>>> So I changed the join on the Medications table to a LEFT OUTER JOIN 
>>> (isn't that right when Medications might not have any matches?), 
>>> which gets back rows, but they all list "num_medications" as 1, even 
>>> though they are actually different.
>>>
>>> This, of course, isn't exactly my schema, so I had to make some other 
>>> changes which I believe are not relevant.
>>>
>>> Chas Douglass
> 
> 
> 
> 
> ---------------------------------------------------------------
> Mckoi SQL Database mailing list  http://www.mckoi.com/database/
> To unsubscribe, send a message to [email protected]
> 
> 



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