Mysql LEFT JOIN -> Get latest topic id / topic title

Question

I'm trying to left join a table where all topics are located. What im trying to do is to list all forums and its sub categories and also list latest topic at the same time.

SELECT root.name AS root_name
   , subcat.name AS subcat_name
   , subcat.id AS subcat_id
   , subcat.description AS subcat_description
   , subcat.safe_url AS subcat_safe_url
   , topics.*
FROM forum_category AS root
LEFT JOIN forum_category AS subcat ON subcat.parent_id = root.id 
LEFT JOIN
(
   SELECT MAX(`last_post_time`) AS aaaa, last_post_time, topic_title
      , topic_id, forum_id
   FROM `forum_topics`
   WHERE 1
   GROUP BY forum_id
) AS topics ON topics.forum_id = subcat.id 
WHERE root.parent_id = 0 
ORDER BY root_name, subcat_name 

But now im kinda stuck :(, Its so close but currently it lists the first topic only in each sub forum and i need the last one but have no idea how.

Answer 1

The problem with your subquery to find the last post is that there's no reason for last_post_time, topic_title, etc. to belong to the row that has MAX(last_post_time).

Think about this query:

SELECT MAX(last_post_time), MIN(last_post_time), topic_title
FROM forum_topics
GROUP BY forum_id

Which topic_title does this return? The one from the row with the greatest post time? The one from the row with the least post time? It's ambiguous -- MySQL can only choose one topic_title from the group arbitrarily. In practice, it chooses from the row that is stored first physically in the group, and that's outside your control, dependent on storage engine implementation, etc.

Here's an alternative design that finds the forum_topics row for which no other forum_topics row exists with a greater last_post_time:

SELECT root.name AS root_name
   , subcat.name AS subcat_name
   , subcat.id AS subcat_id
   , subcat.description AS subcat_description
   , subcat.safe_url AS subcat_safe_url
   , topics.*
FROM forum_category AS root
LEFT JOIN forum_category AS subcat ON subcat.parent_id = root.id 
LEFT JOIN forum_topics AS topics ON topics.forum_id = subcat.id
LEFT JOIN forum_topics AS t2 ON t2.forum_id = subcat.id 
  AND t2.last_post_time > topics.last_post_time
WHERE root.parent_id = 0 AND t2.forum_id IS NULL
ORDER BY root_name, subcat_name