SQL：当多个列指向同一个其他表时如何连接？

Well lets say I have a Message entity that has field Sender and Recipient, both are stored in database by the foreign key of User entity. The table structure for message table may look like this:

好吧，假设我有一个Message实体，它有字段Sender和Recipient，两者都是由User实体的外键存储在数据库中。消息表的表结构可能如下所示：

id int(11) Not Null primary, autoincrement
sender int(11) Not Null
recipient int(11) Not Null
title varchar(50) Not Null
content varchar(1000) Not Null
datesent datetime Not Null
status varchar(10) Not Null

As you see, the message table has two foreign keys(sender and recipient) and both point to the same user table. Of course I can choose lazy load, thus do not write a join query and instead write two separate queries to load users for sender and recipient separately. It works fine if I am just loading one Message from table, but it can be a significant performance hit when you view a list of messages.

如您所见，消息表有两个外键（发件人和收件人），两者都指向同一个用户表。当然我可以选择延迟加载，因此不要编写连接查询，而是编写两个单独的查询来分别为发件人和收件人加载用户。如果我只是从表中加载一个消息，它可以正常工作，但是当您查看消息列表时，它可能是一个重要的性能影响。

So how can I write a SQL query that join message table with user table when I need both sender and recipient columns to be joined? Anyone know the trick to complete this task?

那么当我需要加入发件人和收件人列时，如何编写将消息表与用户表连接的SQL查询？有人知道完成这项任务的诀窍吗？

2 个解决方案

#1

if i true undestand you question try join 'user' table twice with there ids Example:

如果我真的不明白你的问题尝试加入'用户'表两次与其他ids示例：

select * from Message m left join user se on se.id = m.sender_id 
left join user re on re.id = m.recipient_id

#2

something like this should work

这样的事情应该有效

select m.*,s.username,r.username
from message m 
inner join user s on m.sender = s.userid
inner join user r on m.recipient = r.userid

If there are some senders who do not have any entry in user table, then use left join

如果有些发件人在用户表中没有任何条目，则使用左连接

#1