Mysql – Grouping results with left and inner join with zero counts

countgroup byjoin;MySQL

I have a query like 

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`;

This gives me a result set like

+--------------+-------+
|     text     | count |
+--------------+-------+
| 1 (positive) |   114 |
| 2            |    48 |
| 3 (neutral)  |    34 |
| 4            |     6 |
| 5 (negative) |     0 |
+--------------+-------+

So the problem is that I then need to filter the results further on the participants.appraisee_id column. However if I add this to the where clause I lose my zero result (since the left join returns a null row).

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`;

Which returns

+--------------+-------+
|     text     | count |
+--------------+-------+
| 1 (positive) |    16 |
| 2            |     1 |
+--------------+-------+

When in fact I wish for 

+--------------+-------+
|     text     | count |
+--------------+-------+
| 1 (positive) |    16 |
| 2            |     1 |
| 3 (neutral)  |     0 |
| 4            |     0 |
| 5 (negative) |     0 |
+--------------+-------+

Can anyone help me improve this query?

Thanks

Best Answer

  1. Build a table with n=1..5 and the comments (positive, etc); lets call it labels
  2. Get the counts (5 or fewer)
  3. Put together:

SELECT labels.n, labels.comment, IFNULL(x.ct, 0) AS "count" FROM labels LEFT JOIN ( your big query to get the counts ) x ON x.n = labels.n ORDER BY labels.n

The principle is "FROM (stuff you want all of) LEFT JOIN (stuff that may be missing some)"

Related Question