CODEWITHSUNDEEP
mysqlsqljoin
Amir-M
Amir-M

Reputation: 13

Join between two tables on multiple column

I am wondering if I can have join between two tables but on different columns? Let me explain because it is different with most of the cases that I've seen...

I have a table for all the messages between users and each user has a unique user id. so In the first table I have:

Tx User Id .......... Rx. User Id .......... Date ............ Message

and in user tables I have 

user Id .............. User name

Can I have a join query that gives me 

Tx User "Name" ........... Rx. User "Name: ....... Date ....... Message

The problem is that in my join apparently I can only define 

SELECT messages.* users.name
FROM messages JOIN
     users
     ON messages.RxId = users.id OR messages.TxId = users.id

which is only 1 field, but as I explained above I need 2 field as Rx user name and Tx. User Name based on which id in my messages table is matched.

Thanks a lot in advanced.

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You want two joins. And for this you need to learn about table aliases (a good thing):

SELECT m.*, urx.name, utx.name
FROM messages m LEFT JOIN
     users urx
     ON m.RxId = urs.id LEFT JOIN
     users utx
     ON m.TxId = utx.id;

Upvotes: 2

Related Questions