一个表有userID1和userID2，如何从一个User表中获得User1Name和User2Name

[张天星]

一个表有userID1和userID2，如何从一个User表中获得User1Name和User2Name.
Message表有UserID和SendUserID字段。
User表有Name和RoleID字段。
Role表有Name字段。
如何通过Message表，去关联Role和User，获取UserName,UserRoleName,SendUserName,SendUserRoleName?
。。。
如果只有一个UserID，我还会写多表查询，
可是现在有两个UserID，我就不知道怎么写了。

[学海无涯-回头是岸]

说得好乱啊。。

[octwind]

create temporary table if not exists tmp_userrole 
select a.id userid, b.id roleid, a.name username,b.name rolename FROM user a inner join role b on a.roleid = b.roleid;
select 
(select name from user where userid = t.userid) UserName
,(select rolename from tmp_userrole where userid = t.userid) UserRoleName
,(select name from user where userid = t.SendUserID) SendUserName
,(select rolename from tmp_userrole where userid = t.SendUserID) SendUserRoleName
from Message t ;

[带金箍的至尊宝]

SELECT 
    user.Name AS UserName,
    role.Name AS UserRoleName,
    sendUser.Name AS SendUserName,
    sendRole.Name AS SendUserRoleName
FROM
    Message
        LEFT JOIN
    User user ON Message.UserID1 = user.id
        LEFT JOIN
    Role role ON user.RoleID = role.id
        LEFT JOIN
    User sendUser ON Message.UserID1 = sendUser.id
        LEFT JOIN
    Role sendRole ON sendUser.RoleID = sendRole.id