Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 
0|3
1|10
2|0 --this needs to show as a zero and not be skipped
3|5

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:

**dbo.tblSurveyAnswerChoice
 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:

**dbo.tblILDSlide
 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)
**dbo.tblResponses
 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:

**dbo.tblSlideResponseInfo
 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.

share|improve this question
    
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 '11 at 20:43
1  
Can you add sample input data too please? –  gbn Jun 11 '11 at 7:24
1  
Your question at Stackoverflow is more clear. I have posted an answer there. stackoverflow.com/questions/6300592/… –  krubo Jun 11 '11 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 '11 at 18:12

1 Answer 1

up vote 2 down vote accepted

(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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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