[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.

[si_the_geek]

That is perfectly normal behaviour - joins bring together all rows that match the conditions, even if it means values from the other table(s) are repeated.

Presumably it returns the rows as you want if you leave out the AdditionalComments table (and AnswerText2, which you don't need anyway).

If that is the case, you need to decide which row to get from AdditionalComments when there are multiple to choose from (eg: do you want "ok ok" or "yes"? ..and why did you choose that one rather than the other?)

[usamaalam]

AdditionalComments table has FKParticipantID and FKSurveyQuestionID with a text field AdditionalComments that contains actual text data. AdditionalComments should come based on FKParticipantID and FKSurveyQuestionID.

The point is for each question, participants enter data in AnswerText and also enter comments in AdditionalComments so the additional comments should be associated with specific QuestionID and ParticipantID. Currently it's bringing additional comments not related to both QuestionID and ParticipantID hence the records are duplicating. In ideal condition, AnswerID field will not duplicate.

Thanks.

[usamaalam]

Let me show you data in my table. The first image shows SurveyQuestion, second image shows AnswerText and the third one shows AdditionalComments. Please see "ok ok" is once and only once in additional comments but it's duplicating when I am getting data using sp, so I need to fix the statement.

Hope this makes sense.

Thanks.

[si_the_geek]

AdditionalComments should come based on FKParticipantID and FKSurveyQuestionID.

In that case, conditions for both of those fields need to be specified in the Join, eg:

LEFT JOIN  [AdditionalComments]          
  ON ([SurveyQuestion].[ID] = [AdditionalComments].[FKSurveyQuestionID]
      AND [AdditionalComments].[FKParticipantID] = [AnswerText].[FKParticipantID] )

I guess this is what you were trying to do with [AnswerText2], however that is basically an entirely different table - it is just another copy of [AnswerText], which is not related to the first one.

As you don't actually use [AnswerText2], you should not have it in the query (it is just a potential cause of issues like this one).

[usamaalam]

I'll test it, it seems it will work. Thanks a lot.