Re: How to do this in Mckoi

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

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.
>
> 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]