Help with join

[silent11]

I am practicing with MySQL and joins and I was wondering if any of you could help me out. I created a little test table, and I want to select the user's name in place of their id when I select all of the content from the messages table.

here are the 2 tables I'm working with


mysql> describe messages;
+-------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+----------------+
| id | int(9) | | PRI | NULL | auto_increment |
| sender_id | int(9) | YES | | NULL | |
| receiver_id | int(9) | YES | | NULL | |
| message | text | YES | | NULL | |
| timestamp | timestamp(14) | YES | | NULL | |
+-------------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> describe messages_users;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| user_id | int(9) | | PRI | NULL | auto_increment |
| email | varchar(255) | YES | | NULL | |
| name | varchar(225) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)



I want the desired ouptup to be something like "Frank sent a message to Bill saying: Hi Bill"

Frank and Bill would be users in the messages_users table, there messages in the messages table referenced by their id's.

TIA

[Tangerine Dream]

Hi, for sample message ID = 1, using JOIN:


SELECT CONCAT(mu1.name, ' sent a message to ',
mu2.name,
' saying: ',
m.message) output
FROM messages m INNER JOIN messages_users mu1
ON mu1.id = m.sender_id INNER JOIN messages_users mu2
ON mu2.id = m.receiver_id
WHERE m.id = 1;



Using subqueries:


SELECT CONCAT((SELECT mu.name FROM messages_users mu WHERE mu.id = m.sender_id ),
' sent a message to ',
(SELECT mu.name FROM messages_users mu WHERE mu.id = m.receiver_id ),
' saying: ',
m.message) output
FROM messages m
WHERE m.id = 1;