Re: How to do this in Mckoi
Chas Douglass <[email protected]>
| Newsgroups | gmane.comp.db.mckoi |
|---|---|
| Message-ID | <[email protected]> |
John Zoetebier wrote: > On Sun, 09 Jan 2005 11:10, Chas Douglass wrote: > >>I'm an SQL neophyte and struggling a bit. >> >> From my studying it looks like I want a "scalar subquery" which Mckoi >>doesn't provide. Is there another way to do this? >> >>A slightly contrived example: >> >>Say I have tables Patient, Diseases, and Medications. Diseases and >>Medications are weak entities related to Patients. >> >>I want to select all patients that have disease x and for each of those >>patients show the count of medications they have taken. >> >>What I think I want is: >> >>SELECT >> Patient.name, >> Patient.birthDate, >> (SELECT COUNT(*) >> FROM Medications >> WHERE Patient.id = Medications.patientId) >> AS num_medications >>FROM Patient, Diseases >>WHERE Patient.id = Diseases.patientId AND >> Diseases.disease = 'x'; >> >>But this doesn't work in Mckoi because of the subquery. >> >>Thanks for any help. > > > In these complex cases most of the time I split the query into 2 or more > separate queries. > 1) Get all patients with disease x > 2) For each record of (1) run a query to count the number of medications for > this patient. > > Easier to write and test. > Yes, I can see that. Unfortunately, this would really be easier if I could get it into a single query. That way the customer can enter it without my sending them code. Chas Douglass --------------------------------------------------------------- Mckoi SQL Database mailing list http://www.mckoi.com/database/ To unsubscribe, send a message to [email protected]