MySql-嵌套选择-如何从第一个表中选择值

Question

**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

我想知道是否可以从上一张表(DoctorDecision表(中获得患者和医生的姓名？如何选择？我正在尝试进行一些联接，但不确定是否可以从第一个表中获取值。

示例-我如何知道最后一个表中ResultExamID=1的医生、患者和检查名称？

Answer 1

这个查询应该会给你想要的信息：

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

输出(用于您的样本数据(

Name    Name    ExamName
Mary    Jane    Blood Exam

SQLFiddle 演示