nix... men du er velkommen til at gøre denne SQL mere overskuelig:
Planen er at gøre det nemmere at læse...
[SQL]
declare @lang as int, @surveyid as uniqueidentifier
SELECT
ISNULL(Groups.OrderIndex,0) AS tGroupOrder,
ISNULL(QuestionsSelector.OrderIndex,0) AS tQuestionOrder,
Survey.ROW_ID AS SurveyID,
Survey.Folder_FK AS SurveyFolderID,
SurveyText.Text AS SurveyTitle,
SurveyDescription.Text AS SurveyDescription,
Survey.StartDate,
Survey.EndDate,
Survey.Anonymous,
Survey.Layout_FK,
ISNULL(Survey.CustomExitHTML,' ') AS CustomExitHTML,
Survey.AfterSubmit,
ISNULL(SurveyHeaderText.Text,'') AS SurveyHeader,
ISNULL(SurveyFooterText.Text,'') AS SurveyFooter,
ISNULL(SurveyProperties.XMLSettings,'') AS SurveyProperties ,
Groups.ROW_ID AS GroupID,
Groups.Quiz_FK,
GroupText.Text AS GroupText,
Groups.Text_FK AS Group_Label_ID,
Groups.Type AS GroupType,
Groups.AnswerGroup1_FK AS MatrixAnswerGroup1ID,
Groups.AnswerGroup2_FK AS MatrixAnswerGroup2ID,
ISNULL(AnswerGroup2Title.Text,'') AS AnswerGroup2TitleText,
ISNULL(AnswerGroup1Title.Text,'') AS AnswerGroup1TitleText,
Groups.CondShowType AS GroupCondShowType,
Groups.AnswerGroup1Place,
Groups.AnswerGroup2Place,
Groups.AnswerGroup1Layout,
Groups.AnswerGroup2Layout,
Groups.PageBreakAfter,
GroupProperties.XMLSettings AS GroupProperties,
ISNULL(GroupHeaderText.Text,'') AS GroupHeader,
ISNULL(GroupFooterText.Text,'') AS GroupFooter,
--Remove! /
--QuestionsSelector.ROW_ID AS QID,
--QuestionsSelector.Group_FK AS GFK,
QuestionTextSel.Text AS QTXT,
--QuestionTextSel.TextLanguage as QL,
--Remove! \
Questions.ROW_ID AS QuestionID,
Questions.Group_FK,
ISNULL(QuestionText.Text,'Null') AS QuestionText,
QuestionText.TextLanguage,
Questions.Text_FK AS Question_Label_ID,
Questions.AnswerGroup_FK,
Questions.Optional,
Questions.AllowUserComments,
Questions.Layout AS QuestionLayout,
Questions.isHorizontal,
ISNULL(CommentText.Text,'Null') AS CommentText,
Questions.CommentTitle_FK,
Questions.CommentRows,
Questions.CommentColumns,
Questions.CommentTitlePlace,
Questions.AnswerGroupPlace AS AnswerPlace,
Questions.CondShowType AS QuestionCondShowType,
QuestionProperties.XMLSettings AS QuestionProperties,
ISNULL(QuestionFooterText.Text,'') AS QuestionFooter,
ISNULL(QuestionHeaderText.Text,'') AS QuestionHeader
FROM
--|||||
dbo.tblQuiz Survey
INNER JOIN dbo.tblLabelText SurveyText
ON Survey.Text_FK = SurveyText.Label_FK
LEFT OUTER JOIN dbo.tblLabelText SurveyDescription
ON Survey.Description_FK = SurveyDescription.Label_FK
LEFT OUTER JOIN dbo.tblProperties SurveyProperties
ON Survey.Properties_FK = SurveyProperties.ROW_ID
LEFT OUTER JOIN dbo.tblLabelText SurveyHeaderText
INNER JOIN dbo.tblAdditionalText SurveyHeader
ON SurveyHeaderText.Label_FK = SurveyHeader.Label_FK
AND SurveyHeaderText.TextLanguage = @lang
ON Survey.ROW_ID = SurveyHeader.Parent_FK
LEFT OUTER JOIN dbo.tblLabelText SurveyFooterText
INNER JOIN dbo.tblAdditionalText SurveyFooter
ON SurveyFooterText.Label_FK = SurveyFooter.Label_FK
AND SurveyFooterText.TextLanguage = @lang
ON Survey.ROW_ID = SurveyFooter.Parent_FK
--|||||
LEFT OUTER JOIN dbo.tblGroup Groups
ON Groups.Quiz_FK = Survey.Row_ID
LEFT JOIN dbo.tblLabelText GroupText
ON Groups.Text_FK = GroupText.Label_FK
LEFT OUTER JOIN dbo.tblLabelText AnswerGroup1Title
ON Groups.AnswerGroup1Title_FK = AnswerGroup1Title.Label_FK
and AnswerGroup1Title.TextLanguage = @lang
LEFT OUTER JOIN dbo.tblLabelText AnswerGroup2Title
ON Groups.AnswerGroup2Title_FK = AnswerGroup2Title.Label_FK
and AnswerGroup2Title.TextLanguage = @lang
LEFT OUTER JOIN dbo.tblProperties GroupProperties
ON Groups.Properties_FK = GroupProperties.ROW_ID
LEFT OUTER JOIN dbo.tblLabelText GroupHeaderText
INNER JOIN dbo.tblAdditionalText GroupHeader
ON GroupHeaderText.Label_FK = GroupHeader.Label_FK
AND GroupHeaderText.TextLanguage = @lang
ON Groups.ROW_ID = GroupHeader.Parent_FK and GroupHeader.type = 1
LEFT OUTER JOIN dbo.tblLabelText GroupFooterText
INNER JOIN dbo.tblAdditionalText GroupFooter
ON GroupFooterText.Label_FK = GroupFooter.Label_FK
AND GroupFooterText.TextLanguage = @lang
ON Groups.ROW_ID = GroupFooter.Parent_FK and GroupFooter.type = 2
--||||
-- SELECTOR
LEFT OUTER JOIN dbo.tblQuestion QuestionsSelector
ON QuestionsSelector.group_fk = Groups.row_id
LEFT OUTER JOIN dbo.tblLabelText QuestionTextSel
ON QuestionTextSel.Label_FK = QuestionsSelector.Text_FK
and QuestionTextSel.TextLanguage = 1 --all ENG
--||||
LEFT OUTER JOIN dbo.tblQuestion Questions
ON QuestionsSelector.row_ID = Questions.row_id
LEFT OUTER JOIN dbo.tblLabelText QuestionText
ON QuestionText.Label_FK = Questions.Text_FK
and QuestionText.TextLanguage = @lang
LEFT OUTER JOIN dbo.tblLabelText CommentText
ON Questions.CommentTitle_FK = CommentText.Label_FK
AND CommentText.TextLanguage = @lang
LEFT OUTER JOIN dbo.tblProperties QuestionProperties
ON Questions.Properties_FK = QuestionProperties.ROW_ID
LEFT OUTER JOIN dbo.tblLabelText QuestionHeaderText
INNER JOIN dbo.tblAdditionalText QuestionHeader
ON QuestionHeaderText.Label_FK = QuestionHeader.Label_FK
AND QuestionHeaderText.TextLanguage = @lang
ON Questions.ROW_ID = QuestionHeader.Parent_FK
AND QuestionHeader.Type = 1
LEFT OUTER JOIN dbo.tblLabelText QuestionFooterText
INNER JOIN dbo.tblAdditionalText QuestionFooter
ON QuestionFooterText.Label_FK = QuestionFooter.Label_FK
AND QuestionFooterText.TextLanguage = @lang
ON Questions.ROW_ID = QuestionFooter.Parent_FK
AND QuestionFooter.Type = 2
WHERE (Survey.ROW_ID = @SurveyID)
ORDER BY
tGroupOrder, tQuestionOrder
[/SQL]
