How to do this in Mckoi

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.

Chas Douglass



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