Benjamin W Benjamin W - 6 months ago 11
SQL Question

Mysql JOIN table and update 2 columns information

//user
id name
1 A
2 B
3 C

//mail
send receive message
1 2
3 2
2 1

//result
send receive message
A B
C B
B A


I have 2 tables
user
and
mail


user
table stores
name
,
mail
table stores
id
for send and receive

I need to merge
user
table with
mail


send
&
receive
column will display name from
user
table id match

Answer

You can join the mail table with the users table twice - once to get the name of the sender and once to get the name of the receiver:

SELECT s.name, r.name, m.message
FROM   mail m
JOIN   users s ON m.send = s.id
JOIN   users r ON m.recieve = r.id