How to get the number of Admissions and lates one?? 2

[Dado111]

Hi,

We have two tables A person one and Admission one.
the person have name, surname,sex,Birthdate, adress,
and the admission have Admission_id, Patient_id, Admission_date, Discharge_date.

so how can we, list name, surname,latest admission date, and number of times admitted for all women??

please i am stuck with this question so can sombody give me a hand??
Thanks
Dado

 

[fluteplr]

select patient_id, name, surname,
(select max(admission date) from admission where admission.patient_id = patient.patient_id) lastadmission,
(select count(*) from admission where admission.patient_id = patient.patient_id) countadmission
from patient

 

[bperry]

Here is a Join approach...Depending on how large the tables are, this one could generate a more efficient execution plan

SELECT patient.patient_id, patient.name, patient.surname,
max(admission_date) as LastAdmitDate,
count(admission.patient_id) as AdmitCount
FROM patient inner join admission
ON patient.patient_id = admission.patient_id
WHERE patient.sex = 'F'
GROUP by patient.patient_id, patient.name, patient.surname
ORDER by patient.patient_id

If you need to include patients who don't have an admission record yet (if that is even possible), then change the left outer join to an inner join.