CODEWITHSUNDEEP
mysqlsqldatabaseselect
RonanFelipe
RonanFelipe

Reputation: 657

MySql - Nested Select - How to select the value from the first tables?

**Table Doctor**       
*ID        Name       Other Value*
1         Jane           X
2         John           Y
3         Jame           Z

**Table Patient**
*ID        Name         Other Value*
1         Mary           A
2         Mark           B
3         Mel            C

**Table Appointment**
*ID        PatientID      DoctorID  OtherValue*
1             1             1          X
2             3             2          Y

**Table Exam**
*ID         ExamName*
1         Blood Exam
2         Pregnant Exam

**Table RequestExam**
*ID    AppointmentID   ExamID*
1          1             1
2          2             2

**Table ResultExam**
*ID       RequestExamID      OtherValues*
1              1                XYZA
2              2                ABCD

**Table DoctorDecision**
*ID       ResultExamID       OtherValues*
1             1                 Qwerty
2             2                 Asdfgh

I would like to know If from the last table (Table DoctorDecision) can I get the patient and doctor names? How would be a select to make it? I'm trying to make some joins, but not sure If is possible to get the values from the first tables.

Example - How I would know the name of the doctor, patient name and exam name who has the ResultExamID = 1 in the last table?

Upvotes: 0

Views: 113

Answers (1)

Nick
Nick

Reputation: 147206

This query should give you the information you want:

SELECT p.Name, d.Name, e.ExamName
FROM DoctorDecision dd
JOIN ResultExam re ON re.ID = dd.ResultExamID
JOIN RequestExam qe ON qe.ID = re.RequestExamID
JOIN Exam e ON e.ID = qe.ExamID
JOIN Appointment a ON a.ID = qe.AppointmentID
JOIN Patient p ON p.ID = a.PatientID
JOIN Doctor d ON d.ID = a.DoctorID
WHERE dd.ResultExamID = 1

Output (for your sample data)

Name    Name    ExamName
Mary    Jane    Blood Exam

Demo on SQLFiddle

Upvotes: 2

Related Questions