Re: How to do this in Mckoi
Chas Douglass <[email protected]>
| Newsgroups | gmane.comp.db.mckoi |
|---|---|
| Message-ID | <[email protected]> |
Thanks for the thorough follow-up. I must have messed something up translating to my actual schema. I will look into it further. Chas Douglass 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]