Re: How to do this in Mckoi

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.

-- 
John Zoetebier
Web site: http://www.transparent.co.nz



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