title: How to find total number of products under each category using mysql joins?
body: <p>I have 2 tables - catgories and products. I want to get total number of products falling under each category? My tables look as:</p>
  
  <p>table <strong>categories</strong></p>
  
  <pre><code>category_id     category_name
  1               first category
  2               second category
  3               third category
  </code></pre>
  
  <p>table <strong>products</strong></p>
  
  <pre><code>product_id     product_name     category_id
  1              first product    1
  2              second product   1
  3              third product    1
  4              fourth product   3
  5              fifth product    3
  </code></pre>
  
  <p>I want following output:</p>
  
  <pre><code>category_id     category_name     total_products
  1               first category    3
  2               second category   0
  3               third category    2
  </code></pre>
  
  <p>I am currently using following sql but it is not yielding me correct result:</p>
  
  <pre><code>SELECT `c`.`category_id`, `c`.`category_name`, COUNT(`p`.`product_id`) AS total_products FROM `categories` AS `c` INNER JOIN `products` AS `p` ON `c`.`category_id` = `p`.`product_id` GROUP BY `p`.`category_id`
  </code></pre>