CODEWITHSUNDEEP
sqloracle-databasejoinsubquery
Shandep
Shandep

Reputation: 147

Joins and Nested Queries - multiple tables (4+)

RDMBS: Oracle Nested query is a requirement.

I'm trying to collect all patients who had an appointment in March 2014, and also show what doctor they saw, and what they were diagnosed with. Then display Appointment Id, Patients full name, age, sex and phone, and show the Doctors full name and phone. I am able to do most of this, up until i ask what the patient was diagnosed with.

This code allows me to access the patient and doctor records for march 2014:

Select Appointment.appointmentid, patient.surname ||','|| patient.given as     Patient, trunc(((sysdate-patient.dob)/365),0) as Patient_Age, patient.phonehome as Patient_Contact, doctor.Surname ||','|| doctor.given as Doctor, doctor.phone as Doctor_Contact
from doctor, patient, appointment 
where doctor.doctorid=appointment.doctorid
and patient.patientid=appointment.patientid
and extract(month from dateofappointment) = '03'
and extract(year from dateofappointment) = '2014';

But as soon as I place in the nested query for the diagnosis, i get errors.

code: 

Select Appointment.appointmentid, patient.surname ||','|| patient.given as Patient, trunc(((sysdate-patient.dob)/365),0) as Patient_Age, patient.phonehome as Patient_Contact, disease.name as Diagnosis, doctor.Surname ||','|| doctor.given as Doctor, doctor.phone as Doctor_Contact
from doctor, patient, appointment 
where disease.name in (select disease.name
from disease
where disease.diseaseid=diagnosed.diseaseid
and diagnosed.appointmentid=appointment.appointmentid)
and doctor.doctorid=appointment.doctorid
and patient.patientid=appointment.patientid
and extract(month from dateofappointment) = '03'
and extract(year from dateofappointment) = '2014';

Any corrections or advice would be appreciated.

Upvotes: 0

Views: 112

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79919

You didn't referenced the disease table. You can rewrite the query like this:

Select 
  Appointment.appointmentid, 
  patient.surname ||','|| patient.given as Patient, 
  trunc(((sysdate-patient.dob)/365),0) as Patient_Age, 
  patient.phonehome as Patient_Contact, 
  disease.name as Diagnosis, 
  doctor.Surname ||','|| doctor.given as Doctor, 
  doctor.phone as Doctor_Contact
from 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
where extract(month from dateofappointment) = '03'
  and extract(year from dateofappointment) = '2014';

Or, using the same nested query, you can rewrite it like this:

Select 
  Appointment.appointmentid, 
  patient.surname ||','|| patient.given as Patient, 
  trunc(((sysdate-patient.dob)/365),0) as Patient_Age, 
  patient.phonehome as Patient_Contact, 
  disease.name as Diagnosis, 
  doctor.Surname ||','|| doctor.given as Doctor, 
  doctor.phone as Doctor_Contact
from doctor, appointment, patient, diagnosed, disease
where doctor.doctorid = appointment.doctorid
and appointment.patientid = patient.patientid
and diagnosed.appointmentid=appointment.appointmentid
and disease.diseaseid=diagnosed.diseaseid
and disease.name in (select disease.name
                       from disease
                       where disease.diseaseid=diagnosed.diseaseid)
and extract(month from dateofappointment) = '03'
and extract(year from dateofappointment) = '2014';

Upvotes: 1

Related Questions