Abhishek Motiwale Abhishek Motiwale - 4 months ago 7
SQL Question

How to append one table column records into second table in SQL Server?

I have a two tables:

Employee:

EmployeeID First Name Last Name
------------------------------------
1 ABC XYZ
2 DEF LMN
3 GHI OPQ


Conversation:

ConversationId FromUser ToUser
-----------------------------------
11 1 2
22 1 3
33 2 1
44 3 2


I want output as follows

ConversationId FromUserId ToUserId FromUser ToUser
---------------------------------------------------------------
11 1 2 ABC DEF
22 1 3 ABC GHI
33 2 1 DEF ABC
44 3 2 GHI DEF

Answer

JOIN the Employee table twice, once for from user, and once for to user.

select c.*, fu.firstname, tu.firstname
from conversation c
join Employee fu on c.FromUser = fu.EmployeeID
join Employee tu on c.ToUser = tu.EmployeeID
Comments