sql join on two columns
I have two tables as below:
Users
- ID
- UserName
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
精彩评论