MySQL select only rows with max datetime grouped by foreign

Question

I'm implementing a forum system and I have these tables:

• forums : id, title, created_at
• forum_topics : id, title, forum_id created_at
• forum_replies : id, content, forum_topic_id created_at

I'm trying to get all the forums but I want to add also the last created forum_reply.

I'm really stuck in this. Any help please

For example:

I have 3 forums:

• 1, forum1
• 2, forum2
• 3, forum3

and this topics (id, title, forum_id):

• 1, topic1_forum1, 1
• 2, topic1_forum2, 2
• 3, topic2_forum2, 2
• 4, topic1_forum3, 3
• 5, topic2_forum3, 3

finally, this replies (id, content, forum_topic_id, created_at)

• 1, content, 1, 2015-01-01 00:00:00
• 2, content, 1, 2015-02-01 20:00:00
• 3, content, 2, 2015-02-01 22:00:00
• 4, content, 2, 2015-02-01 22:30:00
• 5, content, 3, 2015-01-01 00:00:00
• 6, content, 4, 2015-01-01 00:00:00
• 7, content, 5, 2015-01-02 20:00:00
• 8, content, 5, 2015-01-03 00:00:00

So my final result should look like this:

(forums.id, forum.title, forum_topics.id, forum_topics.title, forum_replies.id, forum_replies.content, forum_replies.timestamp)

• 1, forum1, 1, topic1_forum1, 2, content, 2015-02-01 20:00:00
• 2, forum2, 2, topic1_forum2, 4, content, 2015-02-01 22:30:00
• 3, forum3, 5, topic2_forum3, 8, content, 2015-01-03 00:00:00

EDIT

SQLFiddle full with tables and data

Answer 1

There are two steps to this process - the first is identifying the most recent post. The second is to retrieve the rest of the information (such as the topic title and reply content) which requires joining to that result set again.

select *
from forums f 
  left join forum_topics ft
    on f.id = ft.forum_id
  left join forum_replies fp
    on ft.id = fp.forum_topic_id
  left join (
    SELECT f.id, MAX(fp.created_at) created_at
      FROM forums f 
        INNER JOIN forum_topics ft
          ON f.id = ft.forum_id
        INNER JOIN forum_replies fp
          ON ft.id = fp.forum_topic_id
      GROUP BY f.id
  ) q
    on fp.created_at = q.created_at
      and f.id = q.id
    where fp.created_at = q.created_at
      or fp.id is null;