mysql - 一次从两个表中选择数据的简单 SQL 查询

Question

我在 node js 中为我的聊天应用程序准备了两个表。

表用户:

      - id (PK, AI)
      - username
      - useremail
      - userpass

表消息:

      - mess_id (PK, AI)
      - mess_to (FK - users.id)
      - mess_from (FK - users.id)
      - mess_txt 
      - timestamp

当用户登录时，我想检索那些向当前用户发送消息的人以及当前用户向其发送消息的人的用户名。

到目前为止我尝试过的查询是

SELECT username FROM users JOIN messages ON users.id = messages.mess_to OR users.id = messages.mess_from WHERE users.id = 1

The above query returns the username of the current user , from each fields where he sent messages or he received the messages, and what I want is to get the names of the users who sent him messages and who got messages from this user.

Answer 1

我会使用 UNION 子查询来获取与当前用户进行过对话的用户的 ID。然后将它与 users 表结合起来。

select u.*
from (
    select mess_to as user_id from messages where mess_from = 1
    union
    select mess_from as user_id from messages where mess_to = 1
) sub
join users u on u.id = sub.user_id