Re: How to do this in Mckoi

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]