[RESOLVED] SQL COUNT Error

[prodigymunky]

I'm using the following select statement to pull in info from a topic and a count of the replies made to that topic. But it doesn't give me a row in the select table for any topics that have no replies ... shouldn't it give a 0 for the COUNT???

SELECT DISTINCT
 t.ID AS tID, 
 t.topTitle AS tTitle,
 t.topDescription AS tDesc,
 t.topCategory AS tCat,
 COUNT(r.repTopic) AS tPosts
FROM
 topics t INNER JOIN
 replies r
ON
 r.repTopic = t.ID
GROUP BY
 t.ID,
 t.topTitle,
 t.topDescription,
 t.topCategory

[chazzy]

you should only have 1 piece in your group by clause, and it should be r.repTopic.

But honestly I've seen better results using something like this:

SELECT DISTINCT
 t.ID AS tID, 
 t.topTitle AS tTitle,
 t.topDescription AS tDesc,
 t.topCategory AS tCat,
 (SELECT COUNT(repTopic) from replies WHERE repTopic = t.ID) AS tPosts
FROM
 topics t

Only because when you do the count, you don't really get a good benefit from the join. Let me know if either of those work.

[prodigymunky]

Thank you so much. It worked like a charm.