get values from master table based on fields in sqlserver

Question

I want to retrieve the values from master table based on transaction table different col value,

My master table will have username,FirstName,lastName.

My transaction table will have Id,CreatedUser,UpdatedUser.

So I want query to get report from sql server ,get FirstName,lastName from master table for createruser and get FirstName,lastName from master table for updateuser.

Ex: Master Table

User ID First Name  Last Name
cer001  Ds  CV
cer002  vb  av

Transaction Table

id  CreatedUser UdatedUser
2323    cer001  cer002

So Report should get the results like

Id  CreatedUser UpdatedUSer
2323    Ds,CV   Vb,av

it should be performance wise also good. Please help how to get this

Answer 1

You are looking for self join

select 
      t.Id, m.FirstName +','+m.LastName as  CreatedUser,
      mm.FirstName +','+mm.LastName as  UpdatdUser
from Master m
inner join Transaction  t on t.CreatedUser = m.[User ID]
inner join Master mm on mm.[User ID]= t.UpdatedUser