quote:

---

DECLARE @ModuleID int
set @ModuleID=38

DECLARE @MFLangLanguage int
set @MFLangLanguage=2

SELECT

CASE
WHEN (LanguagesCreated_TitelFr.Counter > 0 OR LanguagesCreated_TekstFr.Counter > 0) AND
(LanguagesCreated_TitelNl.Counter > 0 OR LanguagesCreated_TekstNl.Counter > 0)
THEN 'NL/FR'
WHEN LanguagesCreated_TitelFr.Counter > 0 OR LanguagesCreated_TekstFr.Counter > 0 THEN 'FR'
WHEN LanguagesCreated_TitelNl.Counter > 0 OR LanguagesCreated_TekstNl.Counter > 0 THEN 'NL'
ELSE '-'
END AS 'LanguagesUsed',

CM_TBL_Titel.DataText as Titel,
CM_TBL_User.DataInt as [User],
CM_TBL_ContactGroup.DataText as ContactGroup,
CM_TBL_Type.DataInt as Type,
CM_TBL_DateCreated.DataDate as DateCreated,
CM_TBL_DateSend.DataDate as DateSend,
CM_TBL_Status.DataInt as Status,
CM_TBL_Validate.DataBit as Validate,
CM_TBL_Type_OptionValue.DataText as TypeOptionValue,
CM_TBL_Status_OptionValue.DataText as StatusOptionValue,
CM_TBL_DateValidated.DataDate as DateValidated,


CM_TBL_Tekst.DataText as Tekst,

CM_TBL_UserData.UserName as UserName,
CM_TBL_UserData.UserFirstName as UserFirstName,
CM_TBL_UserData.UserLEvel as UserLEvel,

Triooo_SM_Pro.ID AS ProID

FROM
Triooo_SM_Pro FULL OUTER JOIN

--Titel
(select * from Triooo_SM_FieldValuesLangText where
Triooo_SM_FieldValuesLangText.ModuleFieldID = 12
and Triooo_SM_FieldValuesLangText.Lang = @MFLangLanguage ) CM_TBL_Titel ON
Triooo_SM_Pro.ID = CM_TBL_Titel.RecordID FULL OUTER JOIN

--LanguagesCreated Titel NL
(select RecordID,Len(DataText) as counter from Triooo_SM_FieldValuesLangText where
Triooo_SM_FieldValuesLangText.ModuleFieldID = 12
and Triooo_SM_FieldValuesLangText.Lang = 1 ) LanguagesCreated_TitelNl ON
Triooo_SM_Pro.ID = LanguagesCreated_TitelNl.RecordID FULL OUTER JOIN

--LanguagesCreated Titel FR
(select RecordID,Len(DataText) as counter from Triooo_SM_FieldValuesLangText where
Triooo_SM_FieldValuesLangText.ModuleFieldID = 12
and Triooo_SM_FieldValuesLangText.Lang = 2 ) LanguagesCreated_TitelFr ON
Triooo_SM_Pro.ID = LanguagesCreated_TitelFr.RecordID FULL OUTER JOIN

--Tekst
(select * from Triooo_SM_FieldValuesLangText where
Triooo_SM_FieldValuesLangText.ModuleFieldID = 28
and Triooo_SM_FieldValuesLangText.Lang = @MFLangLanguage ) CM_TBL_Tekst ON
Triooo_SM_Pro.ID = CM_TBL_Tekst.RecordID FULL OUTER JOIN

--LanguagesCreated Tekst NL
(select RecordID,Len(DataText) as counter from Triooo_SM_FieldValuesLangText where
Triooo_SM_FieldValuesLangText.ModuleFieldID = 28
and Triooo_SM_FieldValuesLangText.Lang = 1 ) LanguagesCreated_TekstNl ON
Triooo_SM_Pro.ID = LanguagesCreated_TekstNl.RecordID FULL OUTER JOIN

--LanguagesCreated Tekst FR
(select RecordID,Len(DataText) as counter from Triooo_SM_FieldValuesLangText where
Triooo_SM_FieldValuesLangText.ModuleFieldID = 28
and Triooo_SM_FieldValuesLangText.Lang = 2 ) LanguagesCreated_TekstFr ON
Triooo_SM_Pro.ID = LanguagesCreated_TekstFr.RecordID FULL OUTER JOIN

--User
(select * from Triooo_SM_FieldValues where
Triooo_SM_FieldValues.ModuleFieldID = 13 ) CM_TBL_User ON
Triooo_SM_Pro.ID = CM_TBL_User.RecordID FULL OUTER JOIN

--ContactGroup
(select * from Triooo_SM_FieldValues where
Triooo_SM_FieldValues.ModuleFieldID = 14 ) CM_TBL_ContactGroup ON
Triooo_SM_Pro.ID = CM_TBL_ContactGroup.RecordID FULL OUTER JOIN

--Type
(select * from Triooo_SM_FieldValues where
Triooo_SM_FieldValues.ModuleFieldID = 16 ) CM_TBL_Type ON
Triooo_SM_Pro.ID = CM_TBL_Type.RecordID FULL OUTER JOIN

--Type Option Value
(Select Triooo_SM_ModuleFieldOptionLangText.DataText as DataText, Triooo_SM_ModuleFieldOptions.ID as ID
from Triooo_SM_ModuleFieldOptions INNER JOIN Triooo_SM_ModuleFieldOptionLangText ON
Triooo_SM_ModuleFieldOptionLangText.ModuleOptionFieldID = Triooo_SM_ModuleFieldOptions.ID
where ModuleFieldID = 16 AND Triooo_SM_ModuleFieldOptionLangText.Lang = @MFLangLanguage ) CM_TBL_Type_OptionValue ON
CM_TBL_Type.DataInt = CM_TBL_Type_OptionValue.ID FULL OUTER JOIN

--DateCreated
(select * from Triooo_SM_FieldValues where
Triooo_SM_FieldValues.ModuleFieldID = 18 ) CM_TBL_DateCreated ON
Triooo_SM_Pro.ID = CM_TBL_DateCreated.RecordID FULL OUTER JOIN

--DateSend
(select * from Triooo_SM_FieldValues where
Triooo_SM_FieldValues.ModuleFieldID = 19 ) CM_TBL_DateSend ON
Triooo_SM_Pro.ID = CM_TBL_DateSend.RecordID FULL OUTER JOIN

--Status
(select * from Triooo_SM_FieldValues where
Triooo_SM_FieldValues.ModuleFieldID = 20 ) CM_TBL_Status ON
Triooo_SM_Pro.ID = CM_TBL_Status.RecordID FULL OUTER JOIN

--Status Option Value
(Select
Triooo_SM_ModuleFieldOptionLangText.DataText as DataText,
Triooo_SM_ModuleFieldOptions.ID as ID
from Triooo_SM_ModuleFieldOptions INNER JOIN Triooo_SM_ModuleFieldOptionLangText ON
Triooo_SM_ModuleFieldOptionLangText.ModuleOptionFieldID = Triooo_SM_ModuleFieldOptions.ID
where ModuleFieldID = 20 AND Triooo_SM_ModuleFieldOptionLangText.Lang = @MFLangLanguage ) CM_TBL_Status_OptionValue ON
CM_TBL_Status.DataInt = CM_TBL_Status_OptionValue.ID FULL OUTER JOIN

--Validate
(select * from Triooo_SM_FieldValues where
Triooo_SM_FieldValues.ModuleFieldID = 21 ) CM_TBL_Validate ON
Triooo_SM_Pro.ID = CM_TBL_Validate.RecordID FULL OUTER JOIN

--DateValidated
(select * from Triooo_SM_FieldValues where
Triooo_SM_FieldValues.ModuleFieldID = 30 ) CM_TBL_DateValidated ON
Triooo_SM_Pro.ID = CM_TBL_DateValidated.RecordID

INNER JOIN

-- Triooo_SM_Users
(select
Triooo_SM_Users.Name as UserName,
Triooo_SM_Users.FirstName as UserFirstName,
Triooo_SM_Users.ID as UserID,
Triooo_SM_UserLevels.UserLEvel as UserLEvel
from Triooo_SM_Users INNER JOIN Triooo_SM_UserLevels ON
Triooo_SM_Users.UserLevel = Triooo_SM_UserLevels.ID) CM_TBL_UserData ON
CM_TBL_User.DataInt = CM_TBL_UserData.UserID


WHERE Triooo_SM_Pro.ModuleID = @ModuleID

---