How to join multiple columns in two tables for same data in SQL Server

Question

User table has userid and Username columns. Location table has createduserid and modifieduserid columns.

I want to display createdusername and modifieduser using join between these two tables.

This is my query:

SELECT Top 
    CU.UserName AS [Created UserName], LG.CreatedBy, 
    cu.UserName AS [Modified UserName], lg.ModifiedBy
FROM 
    locationtablep as LG
JOIN 
    Usertable as CU ON CU.CoreUserID = lg.ModifiedBy

Using this query I get only Modified user name but not the createduser name.

Help me to get both username in single query?

You're pulling CU.Username twice and assigning it to different names. — durbnpoisn
– durbnpoisn, Commented Oct 13, 2015 at 20:25
Yes, because I want to display created user name and modified user name on the basis of their userid. — Rock Linux
– Rock Linux, Commented Oct 13, 2015 at 20:34

James Z · Accepted Answer · 2015-10-13 20:26:24Z

3

You need to join the same table twice:

SELECT CU.UserName AS [Created UserName],  LG.CreatedBy, 
       MU.UserName AS [Modified UserName], LG.ModifiedBy
FROM locationtablep as LG
JOIN Usertable as CU
  ON CU.CoreUserID = lg.CreatedBy
JOIN Usertable as MU
  ON MU.CoreUserID = lg.ModifiedBy

answered Oct 13, 2015 at 20:26

James Z

12.3k10 gold badges27 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rock Linux Over a year ago

Thanks for response. After using above query I get same user name in created username and Modified username columns. Actually data is created by some one and edited by some other person. So name is different.

James Z Over a year ago

That's why the different joins are joining to CreatedBy and ModifiedBy columns

Rock Linux Over a year ago

Correct. Let me explain one more time. Example : I have created a post. And after some time that post edited by my colleague. Suppose my userid is 001 and my colleague id 006. So Createdby 001 and Modifiedby 006. The result should Created UserName : LinuxRakesh CreateBy :001 Modified UserName : LinuxKapoor Modifieby: 006. But using your query I get same username in created Username and Modified user column. Created UserName : LinuxRakesh CreateBy :001 Modified UserName : LinuxRakesh Modifieby: 006. I want the username on basis of their id's.

James Z Over a year ago

If CoreUserID is the correct field in Usertable then that's what this does, if there actually is also UserID field that has different values, then of course you have to use that.

Collectives™ on Stack Overflow

How to join multiple columns in two tables for same data in SQL Server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related