View Single Post
06-07-2012, 03:02 PM
#2
Village Genius is offline Village Genius
Village Genius's Avatar
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
 
Posts: 6,894
iTrader: 18 / 100%
 

Village Genius will become famous soon enough

  Old

Select all the topics you want, left join all the relevant comments, group them by topic ID and make one of your rows a count of that ID.

Code:
SELECT 
	t.id.
	t.author,
	... (additional info you want from the topic table here)
	c.forumtopicid,
	COUNT(t.id) as reply_count
FROM
	forum_topics t
LEFT JOIN 
	forum_comments c ON c.forumtopicid=t.id
GROUP BY
	t.id
WHERE
	t.category=1
If this query does not make sense see what it looks like without the GROUP BY statement and it might help.

Thanked by:
Dan (06-13-2012)