title: Trouble with MySQL joining tables and using COUNT function
body: <p>I'm trying to build a forum that consists of three tables. A forum table, a topic table, and a posts table.</p>
  
  <p>On the page displaying all the forums I would like it to show the number of topics for each forum, and the number of posts in that forum. Pretty standard stuff. I'm having some trouble with the MySQL query however. This is what I'm using:</p>
  
  <pre><code>SELECT f.* , COUNT(t.topic_id) AS topics, COUNT(p.post_id) AS posts
  FROM forums AS f
  LEFT JOIN forums_topics AS t ON f.forum = t.forum_id
  LEFT JOIN forums_posts AS p ON t.topic_id = p.topic_id
  GROUP BY f.forum
  </code></pre>
  
  <p>This will return one row for each forum, however the topic count and posts counts return equal to each other. The post count is correct, so the problem is in the topic count.</p>
  
  <p>The tables are structured with forums_posts contains the id number of the topic it belongs to, it does not contain the id number of the forum it belongs to. The forums_topics table contains the id number of the forum it belongs to.</p>
  
  <p>How can my query be corrected? Would it be better if I added the forum_id a post belongs to in the forums_posts table?</p>
  
  <p>Thank you for your advice.</p>