sql-server - 从两个表中选择数据，其中子表的两列具有相同的外键

Question

表格1

StaffId int primary key,
StaffName nvarchar(150)

表2

DoctorIdRef int FOREIGN KEY REFERENCES Table1(StaffId),
AdminIdRef  int FOREIGN KEY REFERENCES Table1(StaffId),

这是我的两个表结构，我想选择像

select DoctorIdRef, DoctorName, AdminIdRef, AdminName

标签： sql-server

Answer 1

select DoctorIdRef, Doctor.StaffName DoctorName, AdminIdRef, Adminr.StaffName AdminName
from Table2 
left join Table1 as Doctor on Doctor.StaffId = Table2.DoctorIdRef 
left join Table1 as Adminr on Adminr.StaffId = Table2.AdminIdRef