Hi, I have the following SQL query setup to return all the forums including no. of posts/topics and the last poster in each forum. I have the following database structure:
forums
- forum_id
- category_id
- forum_name
- display_order
topics
- topic_id
- forum_id
- subject
- last_post (date last post made)
posts
- post_id
- topic_id
- date_line (date posted)
Here's the final query i came up with (with abit of help):
SELECT f.forum_id
, f.category_id
, f.forum_name
, f.display_order
, COUNT(DISTINCT t.topic_id) AS num_topics
, COUNT(p.topic_id) AS num_posts
, MAX(p.date_line) AS last_post
, (SELECT post_id FROM " . posts
WHERE topic_id = (select topic_id
FROM topics
WHERE forum_id = f.forum_id
ORDER BY last_post DESC LIMIT 0, 1) ORDER BY date_line DESC LIMIT 0, 1
) AS last_post_id
, (SELECT user_id FROM posts
WHERE topic_id = (select topic_id
FROM topics
WHERE forum_id = f.forum_id
ORDER BY last_post DESC LIMIT 0, 1) ORDER BY date_line DESC LIMIT 0, 1
) AS last_poster_id
, (SELECT u.username FROM posts p
LEFT OUTER JOIN users u
ON p.user_id = u.user_id
WHERE p.topic_id = (select topic_id
FROM topics
WHERE forum_id = f.forum_id
ORDER BY last_post DESC LIMIT 0, 1) ORDER BY p.date_line DESC LIMIT 0, 1
) AS last_poster
FROM forums f
LEFT OUTER JOIN topics t
ON f.forum_id = t.forum_id
LEFT OUTER JOIN posts p
ON t.topic_id = p.topic_id
GROUP BY f.forum_id
, f.category_id
, f.forum_name
, f.display_order
ORDER BY f.display_order
It seems to be working fine but the idea of the 3 sub queries within, seems abit long winded. I was just wondering if there was an easier and more efficient way. Appreciate the help. Thanks