text: -- =============================================
  -- Author:		<Author,,Name>
  -- Create date: <Create Date,,>
  -- Description:	Returns a table containing ANSWER rows that are relevant
  --              to the assessment's current question mode, standard selection and SAL level.
  -- =============================================
  CREATE PROCEDURE [dbo].[GetRelevantAnswers]
  	@assessment_id int	
  AS
  BEGIN
  	-- SET NOCOUNT ON added to prevent extra result sets from
  	-- interfering with SELECT statements.
  	SET NOCOUNT ON;
  
  	EXECUTE [dbo].[FillEmptyQuestionsForAnalysis]  @Assessment_Id
  
  	-- get the application mode
  	declare @applicationMode varchar(50)
  	exec dbo.GetApplicationModeDefault @assessment_id, @ApplicationMode output
  
  	-- get currently selected sets
  	IF OBJECT_ID('tempdb..#mySets') IS NOT NULL DROP TABLE #mySets
  	select set_name into #mySets from AVAILABLE_STANDARDS where Assessment_Id = @assessment_Id and Selected = 1
  	
  	if(@ApplicationMode = 'Questions Based')	
  	begin
  		
  		select a.assessment_id, a.answer_id, a.is_requirement, a.question_or_requirement_id, a.mark_for_review, 
  			a.comment, a.alternate_justification, a.question_number, a.answer_text, 
  			a.component_guid, a.is_component, a.custom_question_guid, a.is_framework, a.old_answer_id, a.reviewed
  
  			FROM ANSWER a 
  			join NEW_QUESTION c on a.Question_Or_Requirement_Id = c.Question_Id			
  			join (
  				select distinct s.question_id, ns.Short_Name from NEW_QUESTION_SETS s 
  					join AVAILABLE_STANDARDS v on s.Set_Name = v.Set_Name 								
  					join SETS ns on s.Set_Name = ns.Set_Name
  					join NEW_QUESTION_LEVELS l on s.New_Question_Set_Id = l.New_Question_Set_Id
  					join STANDARD_SELECTION ss on v.Assessment_Id = ss.Assessment_Id
  					join UNIVERSAL_SAL_LEVEL ul on ss.Selected_Sal_Level = ul.Full_Name_Sal
  					where v.Selected = 1 and v.Assessment_Id = @assessment_id and l.Universal_Sal_Level = ul.Universal_Sal_Level
  			)	s on c.Question_Id = s.Question_Id		
  			where a.Assessment_Id = @assessment_id 
  			and a.Is_Requirement = 0
  	
  	end
  	else
  	begin		
  
  		select a.assessment_id, a.answer_id, a.is_requirement, a.question_or_requirement_id,a.mark_for_review, 
  			a.comment, a.alternate_justification, a.question_number, a.answer_text, 
  			a.component_guid, a.is_component, a.custom_question_guid, a.is_framework, a.old_answer_id, a.reviewed
  
  			from REQUIREMENT_SETS rs
  				left join ANSWER a on a.Question_Or_Requirement_Id = rs.Requirement_Id
  				left join [SETS] s on rs.Set_Name = s.Set_Name
  				left join NEW_REQUIREMENT req on rs.Requirement_Id = req.Requirement_Id
  				left join REQUIREMENT_LEVELS rl on rl.Requirement_Id = req.Requirement_Id		
  				left join STANDARD_SELECTION ss on ss.Assessment_Id = @assessment_Id
  				left join UNIVERSAL_SAL_LEVEL u on u.Full_Name_Sal = ss.Selected_Sal_Level
  			where rs.Set_Name in (select set_name from #mySets)
  			and a.Assessment_Id = @assessment_id
  			and rl.Standard_Level = u.Universal_Sal_Level 	
  
  	end
  END
  