Hello, I wrote this query to pull out topic names, the author, number of replies and the lastpost. It worked for a bit then started to not work.
SELECT thread.* , user.username, (COUNT(*)-1) as num_replies, MAX(thread.posting_date ) AS max_date FROM `mb_forum_thread`as thread left join mb_users as user on thread.user_id=user.user_id where thread.forum_id = 3 AND (thread.replyto=0 OR thread.replyto=thread.thread_id) group by thread.thread_id ORDER BY max_date DESC
the threads and topics are in one database, topics identified by replyto field=0 and threads identified by replyto field= thread_id.
the thread table is joined with user table to get the author of a topic.
the above query returns the topics only which is right but it fails on the number of replies for each topic and the last post in a topic.