sql - Joins and Nested Queries - multiple tables (4+) -

rdmbs: oracle nested query requirement.

i'm trying collect patients had appointment in march 2014, , show doctor saw, , diagnosed with. display appointment id, patients full name, age, sex , phone, , show doctors full name , phone. able of this, until ask patient diagnosed with.

this code allows me access patient , doctor records march 2014:

select appointment.appointmentid, patient.surname ||','|| patient.given     patient, trunc(((sysdate-patient.dob)/365),0) patient_age, patient.phonehome patient_contact, doctor.surname ||','|| doctor.given doctor, doctor.phone doctor_contact doctor, patient, appointment  doctor.doctorid=appointment.doctorid , patient.patientid=appointment.patientid , extract(month dateofappointment) = '03' , extract(year dateofappointment) = '2014'; 

but place in nested query diagnosis, errors.

code:

select appointment.appointmentid, patient.surname ||','|| patient.given patient, trunc(((sysdate-patient.dob)/365),0) patient_age, patient.phonehome patient_contact, disease.name diagnosis, doctor.surname ||','|| doctor.given doctor, doctor.phone doctor_contact doctor, patient, appointment  disease.name in (select disease.name disease disease.diseaseid=diagnosed.diseaseid , diagnosed.appointmentid=appointment.appointmentid) , doctor.doctorid=appointment.doctorid , patient.patientid=appointment.patientid , extract(month dateofappointment) = '03' , extract(year dateofappointment) = '2014'; 

any corrections or advice appreciated.

you didn't referenced disease table. can rewrite query this:

select    appointment.appointmentid,    patient.surname ||','|| patient.given patient,    trunc(((sysdate-patient.dob)/365),0) patient_age,    patient.phonehome patient_contact,    disease.name diagnosis,    doctor.surname ||','|| doctor.given doctor,    doctor.phone doctor_contact doctor inner join appointment on doctor.doctorid         = appointment.doctorid inner join patient     on patient.patientid       = appointment.patientid inner join diagonsed   on diagnosed.appointmentid = appointment.appointmentid inner join disease     on disease.diseaseid       = diagnosed.diseaseid extract(month dateofappointment) = '03'   , extract(year dateofappointment) = '2014'; 

---

or, using same nested query, can rewrite this:

select    appointment.appointmentid,    patient.surname ||','|| patient.given patient,    trunc(((sysdate-patient.dob)/365),0) patient_age,    patient.phonehome patient_contact,    disease.name diagnosis,    doctor.surname ||','|| doctor.given doctor,    doctor.phone doctor_contact doctor, appointment, patient, diagnosed, disease doctor.doctorid = appointment.doctorid , appointment.patientid = patient.patientid , diagnosed.appointmentid=appointment.appointmentid , disease.diseaseid=diagnosed.diseaseid , disease.name in (select disease.name                        disease                        disease.diseaseid=diagnosed.diseaseid) , extract(month dateofappointment) = '03' , extract(year dateofappointment) = '2014';