title: Grouping results with left and inner join with zero counts
body: <p>I have a query like </p>
  
  <pre><code>SELECT 
      `campaign_question_options`.`text`, 
      COUNT(`campaign_submission_answers`.`answer`) as `count`
      FROM `campaign_questions`
      INNER JOIN `campaign_question_options` ON `campaign_question_options`.`campaign_question_id` = `campaign_questions`.`id`
      LEFT JOIN `campaign_submission_answers` ON `campaign_submission_answers`.`answer` = `campaign_question_options`.`text` AND `campaign_submission_answers`.`campaign_question_id` = 1
      LEFT JOIN `campaign_submissions` ON `campaign_submissions`.`id` = `campaign_submission_answers`.`campaign_submission_id`
      LEFT JOIN `participants` ON `participants`.`id` = `campaign_submissions`.`participant_id`
  WHERE 
      `campaign_questions`.`id` = 1
  GROUP BY `campaign_submission_answers`.`answer` 
  ORDER BY `campaign_question_options`.`index`;
  </code></pre>
  
  <p>This gives me a result set like</p>
  
  <pre><code>+--------------+-------+
  |     text     | count |
  +--------------+-------+
  | 1 (positive) |   114 |
  | 2            |    48 |
  | 3 (neutral)  |    34 |
  | 4            |     6 |
  | 5 (negative) |     0 |
  +--------------+-------+
  </code></pre>
  
  <p>So the problem is that I then need to filter the results further on the <code>participants</code>.<code>appraisee_id</code> column. However if I add this to the where clause I lose my zero result (since the left join returns a null row).</p>
  
  <pre><code>SELECT 
      `campaign_question_options`.`text`, 
      COUNT(`campaign_submission_answers`.`answer`) as `count`
  FROM `campaign_questions`
  INNER JOIN `campaign_question_options` ON `campaign_question_options`.`campaign_question_id` = `campaign_questions`.`id`
  LEFT JOIN `campaign_submission_answers` ON `campaign_submission_answers`.`answer` = `campaign_question_options`.`text` AND `campaign_submission_answers`.`campaign_question_id` = 1
  LEFT JOIN `campaign_submissions` ON `campaign_submissions`.`id` = `campaign_submission_answers`.`campaign_submission_id`
  LEFT JOIN `participants` ON `participants`.`id` = `campaign_submissions`.`participant_id`
  WHERE 
      `campaign_questions`.`id` = 1 AND `participants`.`appraisee_id` = 1
  GROUP BY `campaign_submission_answers`.`answer` 
  ORDER BY `campaign_question_options`.`index`;
  </code></pre>
  
  <p>Which returns</p>
  
  <pre><code>+--------------+-------+
  |     text     | count |
  +--------------+-------+
  | 1 (positive) |    16 |
  | 2            |     1 |
  +--------------+-------+
  </code></pre>
  
  <p>When in fact I wish for </p>
  
  <pre><code>+--------------+-------+
  |     text     | count |
  +--------------+-------+
  | 1 (positive) |    16 |
  | 2            |     1 |
  | 3 (neutral)  |     0 |
  | 4            |     0 |
  | 5 (negative) |     0 |
  +--------------+-------+
  </code></pre>
  
  <p>Can anyone help me improve this query?</p>
  
  <p>Thanks</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I have created a DB dump of the structure which could be useful if any kind people wish to continue helping me on this. <a href="https://gist.github.com/simonbowen/a8316fe91c78b8464402" rel="nofollow">https://gist.github.com/simonbowen/a8316fe91c78b8464402</a></p>