Re: How to do this in Mckoi

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]