CODEWITHSUNDEEP
sqlsql-server
Lobuno
Lobuno

Reputation: 1405

Sql join, 2 tables, same fields

I have 2 tables. To simplify:

Table 1, users:

userId int, userName nvarchar(50)

Table 2 , messages:

msgId int, msgFrom int, msgTo int...

msg1 and msg2, both contain userId. Now I want to get all messages, but instead of the msgFrom I want the user name. I know what to do here:

    select tabMessages.*, tabUsers.userName as Sender 
      from tabMessages 
inner join tabUsers on msgFrom=userId 
where msgId = @someParameter;

Everything works fine and dandy. The same to get the user name instead of msgTo. Now the problem is, how do I do to get BOTH fields in the same call? I want to get the table as

msgId, msgFrom, msgTo, Sender, Recipient. I have tried as:

    select tabMessages.*, tabUsers.userName as Sender, 
           tabUsers.userName as Recipient 
      from tabMessages 
inner join tabUsers on msgFrom=userId and msgTo=userId 
     where msgId = @someParameter;

but that doesn't work. I'm using Ms sql2000 by the way.

Upvotes: 1

Views: 1517

Answers (3)

krico
krico

Reputation: 5728

Did you try a UNION??

http://www.w3schools.com/sql/sql_union.asp

Upvotes: 0

Marcis
Marcis

Reputation: 4617

Join users table twice with different aliases. First join to from column, second to to column.

select m.*, u1.userName as Sender, u2.userName as Recipient 
from tabMessages as m 
  inner join tabUsers as u1 
    on u1.userId=m.msgFrom 
  inner join tabUsers as u2 
    on u2.userId=m.msgTo 
where m.msgId = @someParameter;

Upvotes: 9

Paul
Paul

Reputation: 16843

You need to join onto tabUsers twice. One to get the sender, one to get the recipient:

SELECT m.*, f.userName as Sender, t.userName as Recipient 
FRPOM tabMessages AS m
    INNER JOIN tabUsers AS f on m.msgFrom = f.userId
    INNER JOIN tabUsers AS t on m.msgTo = t.userId 
WHERE m.msgId = @someParameter;

Upvotes: 2

Related Questions