Re: How to do this in Mckoi

--- Chas Douglass <[email protected]> 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
> 
 
In your example, this might work (assuming that med_id is the primary key for
the medications table):

SELECT
   Patient.name,
   Patient.birthDate,
   count (distinct medications.med_id)
   from patient, medications, diseases
   where patient.id = medications.patientId 
   and Patient.id = Diseases.patientId  and Diseases.disease = 'x'
   group by patient.name, patient.birthDate;

This will only show patients that have taken at least one medication. If you
want to include patients who have taken no medication but have disease 'x',
you will probably need to use scalar subqueries, something like this:

SELECT
   Patient.name,
   Patient.birthDate,
   meds.medCount
   from patient
   left join (select medications.patientId as patId,
count(medications.med_id) as medCount
              from medications
              group by medications.patientId) as meds on meds.patId =
Patient.id
   , diseases
   where  Patient.id = Diseases.patientId  and Diseases.disease = 'x';



Take a look at http://mckoi.com/database/mail/subject.jsp?id=6468.

Hope this helps.




=====
M. A. Sridhar


	
		
__________________________________ 
Do you Yahoo!? 
Yahoo! Mail - You care about security. So do we. 
http://promotions.yahoo.com/new_mail


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