DerDu/SPHERE-Framework

SELECT distinct
vP.TblPerson_Id AS TblPerson_Id,

(select vSS.TblStudentSubject_tblStudent
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
limit 0,1) TblStudent_Id,

-- first foreign language
(select vS.TblSubject_Name
from EducationLessonSubject_{{ ConsumerAcronym }}.viewSubject vS
where
(select vSS.TblStudentSubject_serviceTblSubject
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 1
        and vSS.TblStudentSubjectType_Identifier = 'FOREIGN_LANGUAGE'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1)
    = vS.TblSubject_Id ) TblSubject_Name_ForeignLanguage1,

(select tL.Name
from EducationLessonDivision_{{ ConsumerAcronym }}.tblLevel tL
where
(select vSS.TblStudentSubject_serviceTblLevelFrom
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 1
        and vSS.TblStudentSubjectType_Identifier = 'FOREIGN_LANGUAGE'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1) = tL.Id
) TblSubject_From_ForeignLanguage1,

(select tL.Name
from EducationLessonDivision_{{ ConsumerAcronym }}.tblLevel tL
where
(select vSS.TblStudentSubject_serviceTblLevelTill
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 1
        and vSS.TblStudentSubjectType_Identifier = 'FOREIGN_LANGUAGE'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1) = tL.Id
) TblSubject_Till_ForeignLanguage1,

-- second foreign language
(select vS.TblSubject_Name
from EducationLessonSubject_{{ ConsumerAcronym }}.viewSubject vS
where
(select vSS.TblStudentSubject_serviceTblSubject
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 2
        and vSS.TblStudentSubjectType_Identifier = 'FOREIGN_LANGUAGE'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1)
    = vS.TblSubject_Id )
TblSubject_Name_ForeignLanguage2,

(select tL.Name
from EducationLessonDivision_{{ ConsumerAcronym }}.tblLevel tL
where
(select vSS.TblStudentSubject_serviceTblLevelFrom
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 2
        and vSS.TblStudentSubjectType_Identifier = 'FOREIGN_LANGUAGE'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1) = tL.Id
) TblSubject_From_ForeignLanguage2,

(select tL.Name
from EducationLessonDivision_{{ ConsumerAcronym }}.tblLevel tL
where
(select vSS.TblStudentSubject_serviceTblLevelTill
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 2
        and vSS.TblStudentSubjectType_Identifier = 'FOREIGN_LANGUAGE'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1) = tL.Id
) TblSubject_Till_ForeignLanguage2,

-- third foreign language
(select vS.TblSubject_Name
from EducationLessonSubject_{{ ConsumerAcronym }}.viewSubject vS
where
(select vSS.TblStudentSubject_serviceTblSubject
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 3
        and vSS.TblStudentSubjectType_Identifier = 'FOREIGN_LANGUAGE'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1)
    = vS.TblSubject_Id )
TblSubject_Name_ForeignLanguage3,

(select tL.Name
from EducationLessonDivision_{{ ConsumerAcronym }}.tblLevel tL
where
(select vSS.TblStudentSubject_serviceTblLevelFrom
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 3
        and vSS.TblStudentSubjectType_Identifier = 'FOREIGN_LANGUAGE'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1) = tL.Id
) TblSubject_From_ForeignLanguage3,

(select tL.Name
from EducationLessonDivision_{{ ConsumerAcronym }}.tblLevel tL
where
(select vSS.TblStudentSubject_serviceTblLevelTill
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 3
        and vSS.TblStudentSubjectType_Identifier = 'FOREIGN_LANGUAGE'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1) = tL.Id
) TblSubject_Till_ForeignLanguage3,

-- fourth foreign language
(select vS.TblSubject_Name
from EducationLessonSubject_{{ ConsumerAcronym }}.viewSubject vS
where
(select vSS.TblStudentSubject_serviceTblSubject
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 4
        and vSS.TblStudentSubjectType_Identifier = 'FOREIGN_LANGUAGE'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1)
    = vS.TblSubject_Id )
TblSubject_Name_ForeignLanguage4,

(select tL.Name
from EducationLessonDivision_{{ ConsumerAcronym }}.tblLevel tL
where
(select vSS.TblStudentSubject_serviceTblLevelFrom
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 4
        and vSS.TblStudentSubjectType_Identifier = 'FOREIGN_LANGUAGE'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1) = tL.Id
) TblSubject_From_ForeignLanguage4,

(select tL.Name
from EducationLessonDivision_{{ ConsumerAcronym }}.tblLevel tL
where
(select vSS.TblStudentSubject_serviceTblLevelTill
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 4
        and vSS.TblStudentSubjectType_Identifier = 'FOREIGN_LANGUAGE'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1) = tL.Id
) TblSubject_Till_ForeignLanguage4,

-- religion
(select vS.TblSubject_Name
from EducationLessonSubject_{{ ConsumerAcronym }}.viewSubject vS
where
(select vSS.TblStudentSubject_serviceTblSubject
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 1
        and vSS.TblStudentSubjectType_Identifier = 'RELIGION'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1)
    = vS.TblSubject_Id )
TblSubject_Name_Religion,

-- profile
(select vS.TblSubject_Name
from EducationLessonSubject_{{ ConsumerAcronym }}.viewSubject vS
where
(select vSS.TblStudentSubject_serviceTblSubject
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 1
        and vSS.TblStudentSubjectType_Identifier = 'PROFILE'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1)
    = vS.TblSubject_Id )
TblSubject_Name_Profile,

-- orientation
(select vS.TblSubject_Name
from EducationLessonSubject_{{ ConsumerAcronym }}.viewSubject vS
where
(select vSS.TblStudentSubject_serviceTblSubject
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 1
        and vSS.TblStudentSubjectType_Identifier = 'ORIENTATION'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1)
    = vS.TblSubject_Id )
TblSubject_Name_Orientation,

-- elective
(select vS.TblSubject_Name
from EducationLessonSubject_{{ ConsumerAcronym }}.viewSubject vS
where
(select vSS.TblStudentSubject_serviceTblSubject
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 1
        and vSS.TblStudentSubjectType_Identifier = 'ELECTIVE'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1)
    = vS.TblSubject_Id )
TblSubject_Name_Elective1,
(select vS.TblSubject_Name
from EducationLessonSubject_{{ ConsumerAcronym }}.viewSubject vS
where
(select vSS.TblStudentSubject_serviceTblSubject
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 2
        and vSS.TblStudentSubjectType_Identifier = 'ELECTIVE'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1)
    = vS.TblSubject_Id )
TblSubject_Name_Elective2,
(select vS.TblSubject_Name
from EducationLessonSubject_{{ ConsumerAcronym }}.viewSubject vS
where
(select vSS.TblStudentSubject_serviceTblSubject
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 3
        and vSS.TblStudentSubjectType_Identifier = 'ELECTIVE'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1)
    = vS.TblSubject_Id )
TblSubject_Name_Elective3,

-- team
(select vS.TblSubject_Name
from EducationLessonSubject_{{ ConsumerAcronym }}.viewSubject vS
where
(select vSS.TblStudentSubject_serviceTblSubject
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 1
        and vSS.TblStudentSubjectType_Identifier = 'TEAM'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1)
    = vS.TblSubject_Id )
TblSubject_Name_Team1,
(select vS.TblSubject_Name
from EducationLessonSubject_{{ ConsumerAcronym }}.viewSubject vS
where
(select vSS.TblStudentSubject_serviceTblSubject
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 2
        and vSS.TblStudentSubjectType_Identifier = 'TEAM'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1)
    = vS.TblSubject_Id )
TblSubject_Name_Team2,
(select vS.TblSubject_Name
from EducationLessonSubject_{{ ConsumerAcronym }}.viewSubject vS
where
(select vSS.TblStudentSubject_serviceTblSubject
    from PeopleMeta_{{ ConsumerAcronym }}.viewStudentSubject vSS
    where vSS.TblStudentSubjectRanking_Identifier = 3
        and vSS.TblStudentSubjectType_Identifier = 'TEAM'
        and vSS.TblStudent_serviceTblPerson = vP.TblPerson_Id
    limit 0,1)
    = vS.TblSubject_Id )
TblSubject_Name_Team3

FROM PeoplePerson_{{ ConsumerAcronym }}.viewPerson vP
    LEFT JOIN PeopleGroup_{{ ConsumerAcronym }}.viewPeopleGroupMember vPGM on vPGM.TblMember_serviceTblPerson = vP.TblPerson_Id

WHERE vPGM.TblGroup_MetaTable = 'STUDENT'