SELECT a1.OptionID, a2.OptionID, COUNT(*) AS Options FROM Answer AS a1
JOIN Answer AS a2 ON a1.AnswerHeaderID = a2.AnswerHeaderID
WHERE a1.OptionID IN (SELECT OptionID FROM [Option] WHERE QuestionID = @q1) AND a2.OptionID IN (SELECT OptionID FROM [Option] WHERE QuestionID = @q2)
GROUP BY a1.OptionID, a2.OptionID
ORDER BY a1.OptionID, a2.OptionID
Basically what I am trying to accomplish is that I've got a survey that people can answer and I want to be able to join TWO questions with each other.
Let's say Question #1 is SEX - 2 options, male or female
Let's say Question #2 is AGE - 6 options, 0-25, 25-30, 31-40, 41-50, 51-60 or 60+
Let's also say that the optionID are like this:
OptionID 1 -> Male
OptionID 2 -> Female
OptionID 3 -> 0-25
OptionID 4 -> 25-30
OptionID 5 -> 31-40
OptionID 6 -> 41-50
OptionID 7 -> 51-60
OptionID 8 -> 60+
There are NO Females that have answered that are 41-50, all other combinations exist in the Answer table
The result tells you that there are 7 male that are 0-25 that answered the survey.. and so on.
But since there are no female aged 41-50 that answered the survey it does not show up in the result.
My question is how to change the query so that the result would show the below as well
OptionID OptionID Options
2 6 0
For referense. To just check the number of let's say Male and Female that has answered without joining a second question I use the following working query:
SELECT COUNT(a.OptionID) AS Options FROM [Option] o LEFT JOIN Answer a ON o.OptionID = a.OptionID WHERE o.QuestionID = @QuestionID GROUP BY o.OptionIndex ORDER BY o.OptionIndex
Also note that I would like to ORDER BY OptionIndex rather than the OptionID in the query just like I do on the above working query since that is the actual order of the options. In this simple example the OptionID happens to be the correct order as well, but that does not need to be the case.
Thank you VERY much in advance.
I would also like to add that the onlything I really need as result is a 1 column result:
, COUNT(a2.OptionID) AS Options
FROM [Option] AS o1
JOIN [Option] AS o2
JOIN Answer AS a1
ON a1.OptionID = o1.OptionID
JOIN Answer AS a2
ON a2.OptionID = o2.OptionID
AND a2.AnswerHeaderID = a1.AnswerHeaderID
WHERE o1.QuestionID = @q1
AND o2.QuestionID = @q2