title: COUNT, GROUP BY, INNER JOIN, php, mysql
body: <p>I am currently (trying to) develop a forum. Now I've run into a small obstacle. I've four tables in my database; "categories", "forums", "topics" and "replies". They all have an id, a parent id and a name.</p>
  
  <p>My problem is now how I should store and get the amount of topics and replies for each category and forum. At first I thought of saving all the topics and replies in every category and forum, as well as save all the replies in every topic.</p>
  
  <p>But that is something I'd rather try to avoid seeing as I have to do 1x insert and 3x update for every reply made; 1 to category, 1 to forum, 1 to topic and finally the reply itself, an insert.</p>
  
  <p>So instead I thought if it somehow was possible (and if so how) to make a query with COUNT, GROUP BY and INNER JOIN</p>
  
  <p>Something along the line</p>
  
  <pre><code>SELECT * 
    FROM `categories`
    JOIN (COUNT ( * )
           FROM `topics`
       GROUP BY `parent_id`
          WHERE `parent_id` = `categories`.`id`) as `amount_of_topics`
    JOIN (COUNT ( * )
           FROM `topics`
       GROUP BY `parent_id`
          WHERE `parent_id` = `topics`.`id`) as `amount_of_replies`
  </code></pre>
  
  <p>Just that I don't get it work the way I want to at.</p>
  
  <p>So to fetch all replies and count them for a specific forum, the connection replies and forum got is that parent_id of the reply belongs to a topic whos parent_id belongs to a forum.</p>