sql连接两列(sql join on two columns)
我有两个表格如下:
用户
ID 用户名 电子邮件消息
ID 信息 FROMUSER TOUSERFROMUSER和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 EmailMessages
ID Message FROMUSER TOUSERFROMUSER 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.IDIs 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.IdJust 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更多推荐
发布评论