Ashwani Tiwari Ashwani Tiwari - 2 months ago 8x
MySQL Question

Filter row of table-1 on the basis of table-2 column value , no attachement between table-1 and table -2

I have 7 table like that


clinic_id clinic_name emailId address

One to One mapping between clinic and nurse table


nurse_id nurse_name nurse_emailid clinic_id


doctor_id doctor_name doctor_email

Many to Many Mapping between the clinic and doctor


doctor_clinic_id doctor_id clinic_id


pattient_id patient_name address

Many to Many Mapping between patinet and doctor_clinic


patient_dotor_id doctor_clinic_id patient_id

One to Many Mapping between doctor_clinic_patient and doctor_patient_appointment


appointment_id appointment_time appointment_status patient_doctor_id

appointment_status have three value- 'pending','cancelled','completed'

So, the senario is start from here

  1. Select the clinic_id from the nurse_id (nurse table)

  2. then on the basic of clinic_id , I want to select the doctor_clinic_id (doctor_clinic table)

  3. then on the basis of doctor_clinic_id, I want to select the patient_id

  4. then on the basic of patient_id,I want to select the patient details form the patient table

the problem is that , I want to select only those patient from patient table which have a appointment in appointment table of status 'pending'

I made the following query which work fine , get the patient row from db.But I want to filter this patient result on the basis of appointment status


Select * from patient where patient.patient_id in

(select doctor_patient.patient_id from doctor_patient where

doctor_patient.doctor_clinic_id in
(select doctor_clinic.doctor_clinic_id from doctor_clinic where

doctor_clinic.clinic_id in
(select nurse.clinic_id from nurse where nurse_id = 6) ))


I guess a multiple JOIN will work here:

SELECT patients.* FROM patients JOIN (
  SELECT DISTINCT(p.patient_id) as pat_id FROM clinic c 
  JOIN doctor_clinic dc ON c.clinic_id = dc.clinic_id
  JOIN doctor_clinic_patient dcp ON dcp.doctor_clinic_id = dc.doctor_clinic_id
  JOIN patient p ON p.patient_id = dcp.patient_id
  JOIN doctor_patient_appointment dpa ON dcp.patient_doctor_id = dpa.patient_doctor_id
  WHERE dpa.status = 'pending'
) tmp ON tmp.pat_id = patients.patient_id