sql连接两列(sql join on two columns)

sql连接两列(sql join on two columns)

我有两个表格如下：

用户

ID 用户名 电子邮件

消息

ID 信息 FROMUSER TOUSER

FROMUSER和TOUSER在Users表中具有对ID的外键引用。

现在我想要检索使用用户的电子邮件地址加入用户的消息，如下所示

| ID | Message | FROMEMAIL | TOEMAIL |

我可以写下如下的查询。

SELECT Step1.*, users.email as ToUser FROM (SELECT messages.*, users.email as fromuser FROM messages JOIN users on messages.fromuser = users.ID) as Step1 JOIN users on step1.touser = users.ID

有没有简单的方法，我可以实现这个没有子查询？

I have two tables as below:

Users

ID UserName Email

Messages

ID Message FROMUSER TOUSER

FROMUSER and TOUSER have a Foreign key reference to ID in Users table.

Now I want to retrieve messages doing a join on users with the user's email addresses, as below

| ID | Message | FROMEMAIL | TOEMAIL |

I could write a query as below.

SELECT Step1.*, users.email as ToUser FROM (SELECT messages.*, users.email as fromuser FROM messages JOIN users on messages.fromuser = users.ID) as Step1 JOIN users on step1.touser = users.ID

Is there any simple way I can achieve this without the subquery?

最满意答案

只需加入两次Users表：

SELECT Message, uf.Email AS FromEmail, ut.Email AS ToEmail FROM Messages JOIN Users uf ON Messages.FromUser = uf.Id JOIN Users ut ON Messages.ToUser = ut.Id

Just join the Users table twice:

SELECT Message, uf.Email AS FromEmail, ut.Email AS ToEmail FROM Messages JOIN Users uf ON Messages.FromUser = uf.Id JOIN Users ut ON Messages.ToUser = ut.Id