2

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

3
  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)"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.