CODEWITHSUNDEEP
sql-server
Shyam Pachauri
Shyam Pachauri

Reputation: 41

Select data from two table, where child table's two column have same foreign key

Table1

StaffId int primary key,
StaffName nvarchar(150)

Table2

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

This is my two tables structure and I want to select data like

select DoctorIdRef, DoctorName, AdminIdRef, AdminName

Upvotes: 0

Views: 35

Answers (1)

hagay
hagay

Reputation: 2560

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

Upvotes: 1

Related Questions