CODEWITHSUNDEEP
sqlsql-servert-sql
Zelter Ady
Zelter Ady

Reputation: 6338

tsql join tables with 2 ids

I have this tables (Users and Meetings):

UserId | Name
   1   | John
   2   | Linda
   3   | David

and

UserId_1 | UserId_2 | MeetingDate
   1     |     2    |  15/01/2018
   3     |     2    |  17/01/2018
   1     |     3    |  19/01/2018

How do I select from the 2 tables in order to get in each row the Name associated to each id? I'd like to get something like this:

UserId_1 | UserId_2 | User_1_Name | User_2_Name | MeetingDate
   1     |     2    |    John     |    Linda    |  15/01/2018
   3     |     2    |    David    |    Linda    |  17/01/2018
   1     |     3    |    John     |    David    |  19/01/2018

Thank you.

Upvotes: 1

Views: 206

Answers (4)

Abdul Azeez
Abdul Azeez

Reputation: 885

Another approach is 

SELECT m.UserId_1
    ,m.UserId_2
    ,(select name from Users where Users.id = m.UserId_1) as User_1_Name
    ,(select name from Users where Users.id = m.UserId_2) as User_2_Name
    ,m.MeetingDate
FROM Meetings m

Regards

Abdul

Upvotes: 1

Shane
Shane

Reputation: 9

select Meetings.UserId_1,
Meetings.UserId_2,
isnull(a.Name,'--Unknown--') User_1_Name,
isnull(b.Name,'--Unknown--') User_2_Name,
MeetingDate
from Meetings left join Users a on Meetings.UserId_1 = a.UserID
left join Users b on Meetings.UserId_2 = b.UserID

Upvotes: 1

Rogério Carvalho Batista
Rogério Carvalho Batista

Reputation: 462

You just need two inner join:

SELECT m.UserId_1
    ,m.UserId_2
    ,u1.NAME
    ,u2.NAME
    ,m.MeetingDate
FROM Meetings m
INNER JOIN Users u1 ON u1.UserId = m.UserId_1
INNER JOIN Users u2 ON u2.UserId = m.UserId_2

Upvotes: 5

MatBailie
MatBailie

Reputation: 86706

You just need to alias one or both references to the table which you need to join twice...

SELECT
  user1.UserId     AS UserId_1,
  user2.UserId     AS UserId_2,
  user1.Name       AS User_1_Name,
  user2.Name       AS User_2_Name,
  Meetings.MeetingDate
FROM
  Meetings
INNER JOIN
  Users   AS user1
    ON user1.UserId = Meetings.UserId_1
INNER JOIN
  Users   AS user2
    ON user2.UserId = Meetings.UserId_2

The alias means that you can reference which instance of Users you're referring to without any ambiguity.

Upvotes: 2

Related Questions