Advanced JOIN Query in MySQL?

First of all, sorry for my bad English...

I have a problem with one of my MySQL queries开发者_运维技巧. I have got two tables: messages and replies. The messages table contains a id, discussion_id, content and date field. The replies table contains a id, message_id, content and date field.

I want that for one particular discussion the latest ten messages and their replies are fetched. The messages needs to be sorted on the last reply, the replies needs to be sorted first first.

(Like Facebook)

I can do that like this:

SELECT * FROM messages WHERE discussion_id = 'test' ORDER BY date DESC;

And then use a PHP foreach loop to

SELECT * FROM replies WHERE message_id = 'test' ORDER BY date ASC;

But if I would use that I've 11 queries, which isn't ideal. I was thinking about a join or union, but if I would do that, the replies and messages are not separatable. The list is sorted by replies.date, but the replies and their parent message are not together anymore. I'm using this:

SELECT * FROM messages LEFT JOIN replies ON messages.id = replies.message_id WHERE discussion_id='4cc43b40586b6' ORDER BY replies.date DESC

Is it possible to make one query for this task?

---

What about the 'IN' keyword? You could reduce it to two queries total, since you're looking to keep the replies separate from the messages. The first query is fine already, but for the second query, (I haven't checked this against a database yet, so syntax might be slightly off):

SELECT * 
FROM replies 
WHERE message_id IN (
    SELECT message_id 
    FROM messages 
    WHERE discussion_id = 'test'
) 
ORDER BY date ASC;

edit (per suggestion in comment):

You could also modify your join above for a performance boost; it still gives you two queries total, but you wanted two sets of results anyway:

SELECT * 
FROM replies 
LEFT JOIN messages ON messages.id = replies.message_id 
WHERE discussion_id='4cc43b40586b6' 
ORDER BY replies.date ASC

---

For one query, you would want to get the message id and the reply id as you iterate through the results, sort them there. Otherwise, you'd need more queries.

SELECT
      messages.id AS messageid,
      replies.id AS replyid,
      *WHATEVER OTHER COLUMNS*
 FROM replies, messages 
WHERE replies.message_id = messages.discussion_id
  AND messages.discussion_id = 'test'
ORDER BY replies.date ASC;

I'm not really sure whether this is what you're looking for exactly, but this could get you started.