Can't seem to understand why STUFF isn't working

Question

I've got two tables that store questions and possible answers. The questions table can have multiple possible answers to it.

I want to display the possible answers it can have per question.. For instance lets say I have two questions

1) Hows the weather?
2) Do you love MI?

And the other table I have possible answers

1) Good
1) Fair
2) Yes
2) No

My expecting result would be

1 Good, Fair
2 Yes, No

But instead I get:

1 Good, Fair, Yes, No
2 Yes, No, Good, Fair

Here's what I am doing...

SELECT x.SurveyQuestionID, PossibleAnswers = STUFF((
    SELECT ', ' + Answer FROM SurveyQuestionPossibleAnswer sqpa
    INNER JOIN SurveyQuestion sq
    ON sq.SurveyQuestionID = sqpa.SurveyQuestionID
    FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')   
FROM SurveyQuestionPossibleAnswer AS x
GROUP BY x.SurveyQuestionID

I'm getting the right number of rows due to the group by, but the possible answers seems to be giving me every possible answer for each question.

Answer 1

The problem is that you are missing a way to tie the outer table to the data inside of the STUFF. You can do this by adding a WHERE clause to the subquery:

SELECT x.SurveyQuestionID, 
  PossibleAnswers 
    = STUFF((
              SELECT ', ' + Answer 
              FROM SurveyQuestionPossibleAnswer sqpa
              INNER JOIN SurveyQuestion sq
                ON sq.SurveyQuestionID = sqpa.SurveyQuestionID
              WHERE x.SurveyQuestionID = sq.SurveyQuestionID
              FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')   
FROM SurveyQuestionPossibleAnswer AS x
GROUP BY x.SurveyQuestionID;

See SQL Fiddle with Demo. Or to shorten the code, you could even rewrite the query to:

SELECT x.SurveyQuestionID, 
  PossibleAnswers 
    = STUFF((
              SELECT ', ' + Answer 
              FROM SurveyQuestionPossibleAnswer sqpa
              WHERE x.SurveyQuestionID = sqpa.SurveyQuestionID
              FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')   
FROM SurveyQuestion AS x
GROUP BY x.SurveyQuestionID;

See SQL Fiddle with Demo. Both will give you the same result.