Need help with Join

Question

So I'm trying to build a simple forum. It'll be a list of topics in descending order by the date of either the topic (if no replies) or latest reply. Here's the DB structure:

Topics
id, subject, date, poster

Posts
id, topic_id, message, date, poster

The forum itself will consist of an HTML table with the following headers:
Topic | Last Post | Replies

What would the query or queries look like to produce such a structure? I was thinking it would involve a cross join, but not sure... Thanks in advance.

Answer 1

Of course you can make a query for this, but I advise you to create in Topics table fields 'replies' and 'last post', then update them on every new post. That could really improve your database speed, not now, but the time when you will have thousands of topics.

Answer 2

SELECT *
FROM 
    `Topics`, 
    (
        SELECT *, COUNT(*) AS `replies`
        FROM `Posts`
        GROUP BY `Posts`.`topic_id`
        ORDER BY `Posts`.`date` DESC
    ) AS `TopicPosts`
WHERE `Topics`.`id` = `TopicPosts`.`topic_id` 
ORDER BY `Posts`.`date` DESC

This 'should' work, or almost work in the case it doesn't, but I agree with the other poster, it's probably better to store this data in the topics table for all sorts of reasons, even if it is duplication of data.

Answer 3

The forum itself will consist of an HTML table with the following headers:
Topic | Last Post | Replies

If "Last Post" is meant to be a date, it's simple.

SELECT
  t.id,
  t.subject,
  MAX(p.date) AS last_post,
  COUNT(p.id) AS count_replies
FROM 
  Topics t
  INNER JOIN Posts p ON p.topic_id = t.id
GROUP BY
  t.id,
  t.subject

If you want other things to display along with the last post date, like its id or the poster, it gets a little more complex.

SELECT
  t.id,
  t.subject,
  aggregated.reply_count,
  aggregated.distinct_posters,
  last_post.id,
  last_post.date,
  last_post.poster
FROM 
  Topics t
  INNER JOIN (
    SELECT   topic_id,
             MAX(p.date) AS last_date,
             COUNT(p.id) AS reply_count,
             COUNT(DISTINCT poster) AS distinct_posters
    FROM     Posts
    GROUP BY topic_id
  ) AS aggregated ON aggregated.topic_id = t.id
  INNER JOIN Posts AS last_post ON p.date = aggregated.last_date

As an example, I've added the count of distinct posters for a topic to show you where this approach can be extended.

The query relies on the assumption that no two posts within one topic can ever have the same date. If you expect this to happen, the query must be changed to account for it.