Another SQL command question, now involving inner joins

[GigaCluster]

I have a table that contains two integer values which represent a "to" userid, and a "from" userid.
I have another table that contains an integer value (primary key) plus a username string.

Example:
MESSAGES TABLE
to_userid from_userid
1 2
2 3
5 14

USER TABLE
userid username
1 John
2 Lacey
3 Evelyn

I want to make a query that will display all rows of the messages table, converting both columns to the usernames.

Here's what I came up with that converts just one column:
select user.username, messages.from_userid from messages, user where user.userid = messages.to_userid;

I know that this is a hack and it logically does not fully make sense. Someone, please give me the right query!

 

[Buddha Bart]

Well I tried to start, but I'm too tired. So here's the scripts I used to setup if anyone else wants a try.

CREATE TABLE messages (
to_userid INT,
from_userid INT
);

CREATE TABLE user (
userid INT,
username VARCHAR(10)
);

INSERT INTO messages (to_userid, from_userid) VALUES(1,2);
INSERT INTO messages (to_userid, from_userid) VALUES(2,3);
INSERT INTO messages (to_userid, from_userid) VALUES(2,1);

INSERT INTO user (userid, username) VALUES(1, 'John');
INSERT INTO user (userid, username) VALUES(2, 'Lacy');
INSERT INTO user (userid, username) VALUES(3, 'Evelyn');

 

[Barnaby W. Füi]

SELECT user.username as nameone, user.username as nametwo
FROM user
LEFT JOIN messages
ON messages.to_userid = nameone
AND messages.from_userid = nametwo

... i think

Bah, screw it, you've stumped me.

*waits to see answer*

 

[MonkeyK]

something like this:

SELECT toUser.username, fromUser.username
FROM MESSAGES, USER as toUser, USER as fromUser
where messages.to_userid = toUser.userid
and messages.from_userid = fromUser.userid

you can translate this to the equivalent inner join if that is what you want.

 

[oog]

what you wrote already does an inner join.

if you wanted it in a different syntax, it would be:

SELECT toUser.username, fromUser.username, messages.Message
FROM messages
INNER JOIN user AS toUser ON (messages.to_userid = toUser.userid)
INNER JOIN user AS fromUser ON (messages.from_userid = fromUser.userid)