Advanced JOIN Query in MySQL?

Question

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?

The times I've done this I always have a column in the messages table with a last reply date (or if the data is simple, just a single table for all messages and replies). Then it's easy to sort on that date first then your replies second. Just FYI, it might save you some headaches to optimize this during your insert / update as opposed to just on the fetch. — Jason Benson
– Jason Benson, Commented Nov 29, 2010 at 15:31

tptcat · Accepted Answer · 2014-12-11 20:11:39Z

3

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

edited Dec 11, 2014 at 20:11

tptcat

3,9712 gold badges36 silver badges52 bronze badges

answered Nov 29, 2010 at 15:48

Bob Baddeley

2,2621 gold badge16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cristian Over a year ago

Oh no. Dependent subquery. Rewrite your query as a join

Chris · Accepted Answer · 2010-11-29 16:05:28Z

1

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.

answered Nov 29, 2010 at 16:05

Chris

2,7456 gold badges27 silver badges36 bronze badges

Collectives™ on Stack Overflow

Advanced JOIN Query in MySQL?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related