如何在表和列相同的情况下在一行中获得两个不同的值

Question

我试图让发送消息给另一方的用户和接收消息的用户在每行的一行中。

UserTable
==================================
userId    userName
-------   ---------
23         Name1
24         Name2
25         Name3

Message Table
==================================================
msgId      senderId       reciverId      messsage
-------    ----------     ----------     -------------
2          24             25              blah
3          25             24              ohh really blah
4          23             24              lets do blah



Output I need

msgId      sender       receiver         message
--------   -------      ----------      ---------
2          Name2         Name3           blah
3          Name3         Name2           Ohh really blah
4          Name1         Name2           lets do blah

那么oracle sql查询应该是什么呢

Answer 1

只需将用户表连接到消息表两次：

SELECT
    m.msgId,
    u1.userName AS sender,
    u2.userName AS receiver,
    m.message
FROM Message m
INNER JOIN UserTable u1
    ON m.senderId = u1.userId
INNER JOIN UserTable u2
    ON m.reciverId = u2.userId;