MySql Join Tables

I have 3 tables,

jos_messages => 'message_id (PK)', 'message', 'from_user_id', 'touser id'

jos_message_thread => 'thread_id PK','message_id FK', 'from_user_id', 'to_user_id'

jos_users = > 'user_id(FK)', 'firstname', 'avatar'

I need to make a query with this tables to return the data in one result, data are: last inserted message (jos_messages), firstname and avatar of message sender (jos_users) the result should be some开发者_StackOverflow中文版thing like this:

message: "Hi, this is last inserted messsage"

firstname:"ledif"

avatar:"http://a.com/a.png"

What I have now is this query but the problem is, the returned message is not the last message inserted.

SELECT jmt.from_user_id, jm.message, ju.firstname, ju.avatar
FROM jos_messages_thread AS jmt
LEFT JOIN jos_messages AS jm ON jm.message_id = jmt.message_id
LEFT JOIN jos_comprofiler AS ju ON ju.user_id =     jmt.from_user_id
WHERE jmt.to_user_id ='$user_id'
AND jmt.thread_owner ='$user_id'
GROUP BY jmt.from_user_id

Is there a way to ORDER the jm.message_id in the middle of left join query? to make it something like:

LEFT JOIN jos_messages AS jm ON jm.message_id = (LAST INSERTED) jmt.message_id

Thanks!! btw Im not good in english, Im sorry if my question is not so clear.

You don't need to insert the ORDER BY in the middle of the join, even if there were a way to do that. Just ORDER BY jm.message_id DESC at the end of the whole query, and use LIMIT 1 to limit the result to the first row.

LEFT JOIN jos_messages AS jm ON jm.message_id = (Select jthread.message_id from jos_messages_thread Order By jthread.message_id desc Limit 1)