[eluser]Daniel H[/eluser]
I know I should be posting this in an db forum, but I hope this is an easy problem someone can help me with...
Essentially I am trying to return the comment count for a given post. I'm doing this using a LEFT join and aggregate COUNT, however because I need to left join the category/tags table to the post, the query I'm using multiplies the number of tags/categories by the number of comments!
Here's a simplified query... can anyone suggest how I ought to be doing this? Maybe my comment counts have to be separate queries...?
Code:
SELECT DISTINCT posts.id, posts.title,
COUNT(post_comment.comment_id) AS comment_count,
COUNT(taxonomy.id) AS taxonomy_count
FROM posts
LEFT JOIN post_comment ON posts.id = post_comment.post_id
INNER JOIN comments ON post_comment.comment_id = comments.id
LEFT JOIN post_taxonomy ON posts.id = post_taxonomy.post_id
INNER JOIN taxonomy ON post_taxonomy.taxonomy_id = taxonomy.id
GROUP BY posts.id
ORDER BY posts.id DESC