By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table Corporate (CorporateId INT, DirectorId INT, ManagerId INT, SalesId INT);
Insert Into Corporate Values(1,1,1,1),
(2,2,2,3),
(3,3,4,5);
Create Table Employee(EmployeeId INT, FirstName VARCHAR(50), LastName VARCHAR(50));
Insert Into Employee Values(1,'Tim','Sarah'),
(2,'Tom','Paulsen'),
(3,'Tam','Margo'),
(4,'Eli','Lot'),
(5,'Ziva','Lit');
8 rows affected
SELECT E.EmployeeId, E.FirstName, E.LastName
FROM Employee E JOIN Corporate C
ON E.EmployeeID IN (C.DirectorId ,C.ManagerId ,C.SalesId)
WHERE C.CorporateId=3
| EmployeeId | FirstName | LastName |
|---|---|---|
| 3 | Tam | Margo |
| 4 | Eli | Lot |
| 5 | Ziva | Lit |