title: sql counting all records belong to parent record with many to may relationship
body: <p>I have a Tables called <strong>Category</strong> and <strong>Posts</strong>. a Post can be <em>belong to more than one</em> category. A category has many posts and their relationship is <strong>many to many</strong>. i created a new weak entity <em>cat_post</em> table. in <strong>Category</strong>, a record can have many child but not a child of child. so i want to count all posts belongs to all parent records.</p>
  
  <p>Category table fields</p>
  
  <pre><code>id    category_id
  1
  2
  3       
  4       1
  5       1
  6       2
  7       2
  </code></pre>
  
  <p>Posts table</p>
  
  <pre><code>id 
  1
  2
  3
  </code></pre>
  
  <p>cat_post table field</p>
  
  <pre><code>tender_id   category_id
  1               4
  2               5
  3               3
  3               5
  4               6
  4               4
  </code></pre>
  
  <p>Category:  </p>
  
  <pre><code>+-----------------+--------------+------+-----+---------+----------------+  
  | Field           | Type         | Null | Key | Default | Extra          |  
  +-----------------+--------------+------+-----+---------+----------------+  
  | id              | int(11)      | NO   | PRI | NULL    | auto_increment |  
  +-----------------+--------------+------+-----+---------+----------------+  
  | category_id     | int(11)      | NO   | NULL | NULL   |      NULL      |  
  +-----------------+--------------+------+-----+---------+----------------+  
  </code></pre>
  
  <p>Posts:  </p>
  
  <pre><code>+-------------------+--------------+------+-----+---------+-----------+  
  | Field             | Type         | Null | Key | Default | Extra     |  
  +-------------------+--------------+------+-----+---------+-----------+  
  | id                | int(11)      | NO   | PRI | NULL    |           |  
  +-------------------+--------------+------+-----+---------+-----------+  
  </code></pre>
  
  <p>cat_post:  </p>
  
  <pre><code>+---------------+---------+------+-----+---------+-------+  
  | Field         | Type    | Null | Key | Default | Extra |  
  +---------------+---------+------+-----+---------+-------+  
  | category_id   | int(11) | NO   | PRI | NULL    |       |  
  | post_id       | int(11) | NO   | PRI | NULL    |       |  
  +---------------+---------+------+-----+---------+-------+  
  </code></pre>
  
  <p>i want to count all posts that belongs to parent records. in other word count all posts to each parent record of a category where category.category_id is null only.<br>
  <strong>NB</strong>: if a category record has a child then there will be no post to that record.</p>