Re: How to do this in Mckoi

Using multiple LEFT JOINs can add complications in aggregate queries. 
You can nest aggregrate queries if you need to.  For example,

    SELECT COUNT(something), nested_count
      FROM ( SELECT COUNT(something_else) nested_count
               FROM table_a LEFT JOIN table_b ON ( ... )
           GROUP BY some_field )
         LEFT JOIN
           table_c ON ( ... )
  GROUP BY some_other_field

Or you can use a view to simplify the query.

Toby.

Chas Douglass wrote:

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



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