[RESOLVED] Problem in SQL Statement

[usamaalam]

Hello everybody,

I have a problem in my SQL statement. Here's my stored proc.

CREATE PROCEDURE pcsn_SurveyStatsTextAnswers2

@SurveyID AS INT

AS

SELECT DISTINCT
	 [Survey].[ID],
	 CAST([Survey].[SurveyName] AS VARCHAR(1000)) AS [SurveyName],
	 [SurveyQuestion].[ID] AS [QuestionID],
	 [AnswerText].[ID] AS [AnswerID],
	 CAST([SurveyQuestion].[ReportQuestion] AS VARCHAR(5000)) AS [SurveyQuestion],
	 CAST([AnswerText].[Answer] AS VARCHAR(5000)) AS [Answer],
	 CAST([AdditionalComments].[AdditionalComments] AS VARCHAR(5000)) AS [AdditionalComments],
	 [Customer].[CompanyName] AS [CompanyName],
	 [AnswerText].[FKParticipantID] AS [ParticipantID]
FROM
	[Survey]
INNER JOIN
	[SurveyQuestion]
ON
	[Survey].[ID] = [SurveyQuestion].[FKSurveyID]
INNER JOIN
	[Customer]
ON
	[Survey].[FKCustomerID] = [Customer].[ID]
INNER JOIN
	[AnswerText]
ON
	[SurveyQuestion].[ID] = [AnswerText].[FKSurveyQuestionID]
LEFT JOIN
	[AdditionalComments]
ON
	[SurveyQuestion].[ID] = [AdditionalComments].[FKSurveyQuestionID]
LEFT JOIN
	[AnswerText] AS [AnswerText2]
ON
	[AnswerText2].[FKParticipantID] = [AdditionalComments].[FKParticipantID]
INNER JOIN
	[QuestionType]
ON
	[SurveyQuestion].[FKQuestionTypeID] = [QuestionType].[ID]
WHERE 
	[QuestionType].[Name] = 'Text'
AND
	[Survey].[ID] = @SurveyID
GO

Please see the attached image that shows results. SurveyQuestion table is joined with AdditionalComments table with LEFT JOIN as there can be additional comments with question but not necessary. In the similary way AnswerText table is joined with AdditionalComments to get the additional comments entered by the participant. For one AnswerText record, there can be one or no additional comments.

The results of this query are duplicated. You can see each AnswerText is coming with all the AdditionalComments for the particular question however there should be only one AnswerID.

Please advise.

Thanks.