Hi,
I am making a forum, and working on the part that the query calls all zones in the forum, and the number of posts/replies for that zone. The following code works to call the list of zones and display the number of posts, but not replies.
SELECT forum_cat.ID, forum_cat.ForumID, forum_cat.ForumName, forum_cat.ForumDescription, forum_cat.CreationDate, count(forum_post.ID) AS NumPosts
FROM forum_cat INNER JOIN forum_post ON forum_cat.ID = forum_post.ZoneID
WHERE forum_cat.ForumID = 'varid'
GROUP BY forum_cat.ID, forum_cat.ForumID, forum_cat.ForumName, forum_cat.ForumDescription, forum_cat.CreationDate
How do I adapt the FROM area to call the number of posts AND number of replies. I thought the code might look something like this:
SELECT forum_cat.ID, forum_cat.ForumID, forum_cat.ForumName, forum_cat.ForumDescription, forum_cat.CreationDate, count(forum_post.ID) AS NumPosts, count(forum_reply.ID) AS NumReplies
FROM forum_cat INNER JOIN (forum_post ON forum_cat.ID = forum_post.ZoneID) OR (forum_reply ON forum_cat.ID = forum_reply.ZoneID)
WHERE forum_cat.ForumID = 'varid'
GROUP BY forum_cat.ID, forum_cat.ForumID, forum_cat.ForumName, forum_cat.ForumDescription, forum_cat.CreationDate
but it didn't work. Any suggestions. Also, note that posts and replies are stored in their own tables.
Pls help, not got a lot of time left to finish this!
Steve
