CODEWITHSUNDEEP
mysqlsql
Nelson Galdeman Graziano
Nelson Galdeman Graziano

Reputation: 562

mysql joining 2 tables getting 2 different values from the same table

I have 2 tables that I would like to join...

Table "users":

Columns:

uid => user index

name => user name

Table "messages":

Columns:

fromid => sender user index

toid => receiver user index

message => text message

I want an output like this: | from_username | to_username | message |

I can get | from_username | message | with this query:

SELECT messages.message, users.name FROM messages,users WHERE messages.fromid = users.uid;

Upvotes: 0

Views: 141

Answers (2)

Edper
Edper

Reputation: 9322

Try 

 SELECT sender.name, receiver.name, message
 FROM messages
 INNER JOIN users sender
 ON messages.fromid = sender.uid
 INNER JOIN users receiver
 ON messages.toid = receiver.uid

Upvotes: 0

ModulusJoe
ModulusJoe

Reputation: 1456

You need to complete the join twice:

Select f.name, t.name, message from messages
Left join users as f on messages.fromID = f.uid
Left join users as t On messages.toid = t.uid

For the complete join syntax (and some explanation) the MySQL doc page is not bad http://dev.mysql.com/doc/refman/5.0/en/join.html

Upvotes: 1

Related Questions