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.