开发者

sql join on two columns

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 doi开发者_C百科ng 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?


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜