Multiple Table Query

Hi
I've got two tables...

user
ID | firstname | username

message
ID | text | posterID | moderatorID


I need to do this... Query the message table and retrieve the latest 20 rows order by message.ID (which is simple). But every row needs to have two associated rows from the user table...

Every message has a posterID and a moderatorID both of which point to seperate rows from the user table. So if I basically need to be able to loop through the results in PHP and display the posters firstname as well as the moderators firstname along side the message text. Understand?

I know I could first query message.. and then query user twice but I don't want to do it that way. I need this in one query.

Any ideas? $5 is up for grabs if you can get this going for me.

Thanks!

 

Hi,
if I right understand you

 

Thanks but that's not it.
It's okay, I've decided to tackle this problem another way. Thanks anyway

 

In case you need it sometime else...might not be the optimal solution, but i think it should work

SELECT
v.id as message_id,
y.firstname as moderator_firstname,
y.text as moderator_text,
x.firstname as poster_firstname,
x.text as poster_text

FROM
(SELECT * FROM `test`.`message` ORDER BY `message`.`id` LIMIT 20) v
INNER JOIN (SELECT message.id, user.firstname, message.text FROM `test`.`user` INNER JOIN `test`.`message` ON `user`.`id` = `message`.`posterID`) x ON x.id = v.id
INNER JOIN (SELECT message.id, user.firstname, message.text FROM `test`.`user` INNER JOIN `test`.`message` ON `user`.`id` = `message`.`moderatorID`) y ON y.id = v.id