带有三个表的Sql联接查询

Question

Table1

HospitalID   HospitalName                     Address
    1        Andalusia Regional Hospital      Andaluisa
    2        Baypointe Hospital               New York
    3        Axa Hospital                     California

Table2

ID   HospitalID   ClinicName
 1     1          Women Health
 2     1          Man Health
 3     1          Travel Health
 4     1         Cardiology
 5     2         internal medicine
 6     3         Cardiology

Table3

Id  HospitalID  TargetHospitalID  DistanceKm
 1      1              2           45
 2      1              3           95
 3      2              3           15

我在HospitalID=1医院。(心脏科)

我需要找到离这里最近的医院和我的医院证明。

select top 1 TargetHospitalID 
from Table3 
where HospitalID = 1 
order by DistanceKm

这段代码的结果是TargetHospitalID=2。但是，在Baypointe医院没有心脏病科。

我要找最近的有心脏病科的医院。如何进行此查询？谢谢你的帮助。

Answer 1

您需要修改查询，将其与Table2连接起来，并由部门进行过滤。

select top 1 t3.TargetHospitalID 
from Table3 t3 INNER JOIN Table2 t2 ON t3.TargetHospitalID = t2.HospitalID
where t3.HospitalID=1 and t2.ClinicName = 'Cardiology'
order by t3.DistanceKm