DerDu/SPHERE-Framework

select
    vP.TblPerson_Id AS TblPerson_Id,
    -- Person S1
    -- Definition S1 Id
    (select vRTP.TblToPerson_serviceTblPersonTo
        from SettingConsumer_{{ ConsumerAcronym }}.viewRelationshipToPerson vRTP
        where vP.TblPerson_Id = vRTP.TblToPerson_serviceTblPersonFrom
        and vRTP.TblType_Name like 'Bevollm%chtigt'

        limit 0,1
    ) TblPerson_Authorized_Id,

    if ((select vRTP.TblToPerson_IsSingleParent
        from SettingConsumer_{{ ConsumerAcronym }}.viewRelationshipToPerson vRTP
        where vP.TblPerson_Id = vRTP.TblToPerson_serviceTblPersonFrom
        and vRTP.TblType_Name like 'Bevollm%chtigt'
        limit 0,1
    ), 'Ja', 'Nein') TblToPerson_IsSingleParent_Bev,

    (select vP.TblSalutation_Salutation
        from SettingConsumer_{{ ConsumerAcronym }}.viewPerson vP
        where vP.TblPerson_Id = TblPerson_Authorized_Id
    ) TblSalutation_Salutation_Bev,

    (select vP.TblPerson_Title
        from SettingConsumer_{{ ConsumerAcronym }}.viewPerson vP
        where vP.TblPerson_Id = TblPerson_Authorized_Id
    ) TblPerson_Title_Bev,

    (select vP.TblPerson_FirstName
        from SettingConsumer_{{ ConsumerAcronym }}.viewPerson vP
        where vP.TblPerson_Id = TblPerson_Authorized_Id
    ) TblPerson_FirstName_Bev,

    (select vP.TblPerson_SecondName
        from SettingConsumer_{{ ConsumerAcronym }}.viewPerson vP
        where vP.TblPerson_Id = TblPerson_Authorized_Id
    ) TblPerson_SecondName_Bev,

    (select vP.TblPerson_CallName
        from SettingConsumer_{{ ConsumerAcronym }}.viewPerson vP
        where vP.TblPerson_Id = TblPerson_Authorized_Id
    ) TblPerson_CallName_Bev,

    (select vP.TblPerson_LastName
        from SettingConsumer_{{ ConsumerAcronym }}.viewPerson vP
        where vP.TblPerson_Id = TblPerson_Authorized_Id
    ) TblPerson_LastName_Bev,

    (select vP.TblPerson_BirthName
        from SettingConsumer_{{ ConsumerAcronym }}.viewPerson vP
        where vP.TblPerson_Id = TblPerson_Authorized_Id
    ) TblPerson_BirthName_Bev,

    (select vP.TblCommonBirthDates_Birthday
        from SettingConsumer_{{ ConsumerAcronym }}.viewPerson vP
        where vP.TblPerson_Id = TblPerson_Authorized_Id
    ) TblPerson_Birthday_Bev,

    -- Address
    (select vCA.TblAddress_StreetName
        from SettingConsumer_{{ ConsumerAcronym }}.viewContactAddress vCA
        where vCA.TblPerson_Id = TblPerson_Authorized_Id
        and vCA.TblType_Name = 'Hauptadresse'
    ) TblAddress_StreetName_Bev,

    (select vCA.TblAddress_StreetNumber
        from SettingConsumer_{{ ConsumerAcronym }}.viewContactAddress vCA
        where vCA.TblPerson_Id = TblPerson_Authorized_Id
        and vCA.TblType_Name = 'Hauptadresse'
    ) TblAddress_StreetNumber_Bev,

    (select vCA.TblCity_Code
        from SettingConsumer_{{ ConsumerAcronym }}.viewContactAddress vCA
        where vCA.TblPerson_Id = TblPerson_Authorized_Id
        and vCA.TblType_Name = 'Hauptadresse'
    ) TblCity_Code_Bev,

    (select vCA.TblCity_Name
        from SettingConsumer_{{ ConsumerAcronym }}.viewContactAddress vCA
        where vCA.TblPerson_Id = TblPerson_Authorized_Id
        and vCA.TblType_Name = 'Hauptadresse'
    ) TblCity_City_Bev,

    (select vCA.TblCity_District
        from SettingConsumer_{{ ConsumerAcronym }}.viewContactAddress vCA
        where vCA.TblPerson_Id = TblPerson_Authorized_Id
        and vCA.TblType_Name = 'Hauptadresse'
    ) TblCity_District_Bev,

    (select vCA.TblAddress_County
        from SettingConsumer_{{ ConsumerAcronym }}.viewContactAddress vCA
        where vCA.TblPerson_Id = TblPerson_Authorized_Id
        and vCA.TblType_Name = 'Hauptadresse'
    ) TblAddress_County_Bev,

    (select vCA.TblState_Name
        from SettingConsumer_{{ ConsumerAcronym }}.viewContactAddress vCA
        where vCA.TblPerson_Id = TblPerson_Authorized_Id
        and vCA.TblType_Name = 'Hauptadresse'
    ) TblState_Name_Bev,

    (select vCA.TblAddress_Nation
        from SettingConsumer_{{ ConsumerAcronym }}.viewContactAddress vCA
        where vCA.TblPerson_Id = TblPerson_Authorized_Id
        and vCA.TblType_Name = 'Hauptadresse'
    ) TblAddress_Nation_Bev,

    (select
        group_concat(
            concat(
            vCP.TblType_Name,
            ': ',
            vCP.TblPhone_Number,
            vCP.TblToPerson_Remark
            ) separator ' - \n\r'
        ) from SettingConsumer_{{ ConsumerAcronym }}.viewContactPhone vCP
        where vCP.TblPerson_Id = TblPerson_Authorized_Id
    ) TblPhone_Number_Bev,

    (select
        group_concat(
            concat(
            vCM.TblType_Name,
            ': ',
            vCM.TblMail_Address,
            case when vCM.TblToPerson_Remark <> '' then ' - ' else '' end,
            vCM.TblToPerson_Remark
            ) separator '\n\r'
        ) from SettingConsumer_{{ ConsumerAcronym }}.viewContactMail vCM
        where vCM.TblPerson_Id = TblPerson_Authorized_Id
    ) TblMail_Address_Bev,

    (select
        group_concat(
            concat(
            vCM.TblMail_Address
            ) separator '; '
        ) from SettingConsumer_{{ ConsumerAcronym }}.viewContactMail vCM
        where vCM.TblPerson_Id = TblPerson_Authorized_Id
        and vCM.TblType_Name = 'Privat'
    ) TblMail_AddressPrivate_Bev,

    (select
        group_concat(
            concat(
            vCM.TblMail_Address
            ) separator '; '
        ) from SettingConsumer_{{ ConsumerAcronym }}.viewContactMail vCM
        where vCM.TblPerson_Id = TblPerson_Authorized_Id
        and vCM.TblType_Name like 'Gesch%ftlich'
    ) TblMail_AddressCompany_Bev

from SettingConsumer_{{ ConsumerAcronym }}.viewPerson vP
    JOIN SettingConsumer_{{ ConsumerAcronym }}.viewGroup vG ON vG.TblPerson_Id = vP.TblPerson_Id
where vG.TblGroup_MetaTable = 'STUDENT'