My question went dead after one unhelpful answer on SO here

I need this to be optimized because it will be called often. It is going into a stored procedure.

I am given @slide (tblILDSlide.id) and @sessionid (tblILDSession.id)

I need my results to look like this:

--lets say students had answered a(3),b(10),c(0),d(5) in a survey (test)
 |Answer Counts 
2|0 --this needs to show as a zero and not be skipped

Since questions will each have different numbers of possible answers I need to have the rows seeded from the @@rowcount of Choice for the QID that is here:

 QID (int, not null)
 Sequence (int, null)
 Choice (nvarchar(100), null) --this column will vary and is for choices
 --like true/false, Yes, No, Red Blue, 
 --or even Completely Agree,Somewhat Agree...,Completely Disagree

To get the QID I have to use @slide and:

 id (PK, int, not null)
 slide_type(FK, int, not null)
 question_id(FK, int, null)
 media_url(nvarchar(100), null)

I need a row for every choice (with zeros for choices with no responses). The responses are kept here:

 --there can be more than one response per question 
 --like for multiple-multiple choice (check all that apply)
 response_id(FK, int, not null)
 answer_sequence (int, null) --accepts 1,2,3 etc for choice
 other (nvarchar(50), null) --for text answers

To get them I have to use response_id which I can get by using @sessionid AND @slide here:

 id (PK, int, not null)
 user_id(nvarchar(50), not null)
 slide_id (FK, int, not null)
 response_date (datetime, not null)
 session_id (FK, int, not null)

Copied from the post at stackoverflow: My problem is when there is a possible choice with no answers I get no count. So I know I need to grab the possible choices which are available [in the other table ...] I am thinking I need to return a blank row for every answer that exists in the tblSurveyAnswerChoices then somehow join the other counts to that, but I can't figure it out.

  • This is not going to be close to being an answer, but your question is very difficult to understand. I would consider using Table Variable and a cursor to accomplish it based on what little I really understand.
    – user1875
    Jun 10, 2011 at 20:43
  • 1
    Can you add sample input data too please?
    – gbn
    Jun 11, 2011 at 7:24
  • 1
    Your question at Stackoverflow is more clear. I have posted an answer there. stackoverflow.com/questions/6300592/…
    – krubo
    Jun 11, 2011 at 16:22
  • @krubo I encourage you to post the same answer here. @lazfish I encourage you to try to choose one site for asking questions. However, I'm open to cross-posts.
    – jcolebrand
    Jun 12, 2011 at 18:12

1 Answer 1


(copying my own answer from the same question on StackOverflow)

Your post already includes a good answer: "I am thinking I need to return a blank row for every answer that exists in the tblSurveyAnswerChoices then somehow join the other counts to that."

Here is one way to do so:

select choice, ifnull(countanswervalue,0) 
from tblsurveyanswerchoice
left join 
    (select answer_sequence, count(answer_sequence) as countanswervalue
    from tblslideresponses
    group by answer_sequence) 
    as countanswer on countanswer.answer_sequence=tblsurveyanswerchoice.sequence
/* the where clause is what you already posted */
where qid in (select question_id from tblidslide where id = @slide)

The use of left join ensures that any choice in the first table will be listed. By default, left join would return NULL for items not existing in the second table, but ifnull lets you replace NULL with 0.

NOTE: ifnull in MySQL should be replaced by isnull in TSQL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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