Archives of the TeradataForum

Message Posted: Wed, 02 Sep 2009 @ 19:02:30 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I like #3 best:

Try #1:

     Select
     A.patient_name
     ,A.Patient_age
     ,A.Patient_weight
     ,B.CountHeprin
     ,C.CountWarfarin
     FROM
     Patient A
     ,(Select Count(*) AS CountHeprin
     from drug_taken WHERE drug_name="Heprin" and patient_id=10)B
     ,(Select Count(*) AS CountWarfarin from drug_taken
     WHERE drug_name="Warfarin" and patient_id=10) C
     WHERE
     A.patient_id=10

It runs, but it may not provide the output format you desire.

- or try #2 (better formatting) -

     Select
     A.patient_name
     ,A.Patient_age
     ,A.Patient_weight
     ,B.CountHeprin
     ,C.CountWarfarin
     ,A.patient_id
     FROM
     Patient A
     ,(Select patient_id, Count(*) AS CountHeprin
     from drug_taken WHERE drug_name="Heprin" group by 1)B
     ,(Select patient_id, Count(*) AS CountWarfarin from drug_taken
     WHERE drug_name="Warfarin" group by 1) C
     WHERE
     A.patient_id=10 AND
     A.patient_id=B.patient_id AND
     A.patient_id=C.patient_id

- or try #3 -

     Select
     A.patient_name
     ,A.Patient_age
     ,A.Patient_weight
     ,B.drug_name
     ,B.DRUG_COUNT
     ,A.patient_id
     FROM
     Patient A
     ,(SEL drug_name, count(*) AS DRUG_COUNT
     FROM drug_taken
     WHERE
     drug_name IN ('Heprin','Warfarin') AND
     patient_id=10
     GROUP BY 1) B
     WHERE
     A.patient_id=10 AND
     A.patient_id=B.patient_id

All Untested...

	<Prev	Next>		<<First	<Prev	Next>	Last>>