Sql join, 2 tables, same fields

Posted by Lobuno on Stack Overflow See other posts from Stack Overflow or by Lobuno
Published on 2010-06-05T20:29:04Z Indexed on 2010/06/05 20:32 UTC
Read the original article Hit count: 176

Filed under:

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.

© Stack Overflow or respective owner

Related posts about sql