Just another solution:

DECLARE @Table1 TABLE (

EID INT,

EmployeeName VARCHAR(32),

Title VARCHAR(32),

Number CHAR(12)

);

DECLARE @Table2 TABLE (

EmpDate DATE,

AnalystEID INT NULL,

JourneyEID INT NULL,

AccountantEID INT NULL

);

INSERT INTO @Table1

( EID, EmployeeName, Title, Number )

VALUES ( 1, -- EID - int

'John', -- EmployeeName - varchar(32)

'Analyst', -- Title - varchar(32)

'xxx-xxx-xxxx' -- Number - char(12)

),

( 2, -- EID - int

'Sam', -- EmployeeName - varchar(32)

'Journey Man', -- Title - varchar(32)

'xxx-xxx-xxxx' -- Number - char(12)

),

( 3, -- EID - int

'Sandra', -- EmployeeName - varchar(32)

'Accountant', -- Title - varchar(32)

'xxx-xxx-xxxx' -- Number - char(12)

);

INSERT INTO @Table2

( EmpDate ,

AnalystEID ,

JourneyEID ,

AccountantEID

)

VALUES ( '2012-02-01' , -- EmpDate - date

1 , -- AnalystEID - int

2 , -- JourneyEID - int

3 -- AccountantEID - int

),

( '2012-02-03' , -- EmpDate - date

NULL , -- AnalystEID - int

2 , -- JourneyEID - int

NULL -- AccountantEID - int

),

( '2012-02-04' , -- EmpDate - date

NULL , -- AnalystEID - int

NULL , -- JourneyEID - int

3 -- AccountantEID - int

),

( '2012-02-05' , -- EmpDate - date

1 , -- AnalystEID - int

2 , -- JourneyEID - int

3 -- AccountantEID - int

);

SELECT * FROM @Table1;

SELECT * FROM @Table2;

SELECT

*

FROM

@Table2 t2

OUTER APPLY (SELECT t1.EmployeeName FROM @Table1 t1 WHERE t1.EID = t2.AnalystEID)dt1(AnalystName)

OUTER APPLY (SELECT t1.EmployeeName FROM @Table1 t1 WHERE t1.EID = t2.JourneyEID)dt2(JourneyName)

OUTER APPLY (SELECT t1.EmployeeName FROM @Table1 t1 WHERE t1.EID = t2.AccountantEID)dt3(AccountantName);