Is there a simple way to substitute ID references for a column value in MySQL?

This sounds simple, and probably is...

Alright, so I have two tables, users and messages. Formatted like so:

users
ID | username
1  | im_a_user
2  | another_user

messages
ID | FROM | TO | CONTENT
1  | 1    | 2  | Blah blah blah...
2  | 2    | 1  | Hello, World!
3  | 2    | 1  | Another message.

If you didn't guess, columns FROM and TO are references to the ID from the table users.

Anyhow, I'd like to make a query that returns something like this:

ID | FROM         | TO           | CONTENT
1  | im_a_user    | another_user | Blah blah blah...
2  | another_user | im_a_user    | Hello, Worl开发者_Go百科d!
3  | another_user | im_a_user    | Another message.

I've done this before with JOINS, but I'm a bit rusty, and I was wondering if there was a simpler way.. If not, a query using a JOIN is fine.

JOINs exist exactly for things like that. You need a query like this:

SELECT
    m.id,
    sender.username AS sender,
    recipent.username AS recipient,
    m.content
FROM messages m
    INNER JOIN users sender
    ON sender.id = `m.from`
    INNER JOIN users recipent
    ON recipient.id = m.to

If you have NULLs on columns TO or FROM, change the apropriate INNER JOIN to a LEFT OUTER JOIN.

As a side note, I'd recommend you refrain from using SQL reserved words such as FROM as column names.

I guess a few joins would be the best way to do this.

From the top of my head.. something like this?

SELECT messages.ID, from_user.username, to_user.username, messages.CONTENT FROM messages
LEFT JOIN users as from_user ON (messages.FROM = users.ID)
LEFT JOIN users as to_user ON (messages.TO = users.ID)