USP_DATALIST_STUDENTRELATIONCONSTITUENCYCRITERIA

create procedure dbo.USP_DATALIST_STUDENTRELATIONCONSTITUENCYCRITERIA
as
    set nocount on;

    select STUDENTRELATIONCONSTITUENCYSETTINGS.ID,
           CONSTITUENCYCODE.DESCRIPTION as CONSTITUENCY,
           case STUDENTRELATIONCONSTITUENCYSETTINGS.RELATEDTOCODE 
                when 0 
                    then 'Student'
                else 'Alumni'
           end as RELATEDTOCODE,
           dbo.UDA_BUILDLIST(RELATIONSHIPTYPECODE.DESCRIPTION) as RELATIONSHIPTYPES
    from dbo.STUDENTRELATIONCONSTITUENCYSETTINGS
    inner join dbo.CONSTITUENCYCODE on STUDENTRELATIONCONSTITUENCYSETTINGS.CONSTITUENCYCODEID = CONSTITUENCYCODE.ID
    left join dbo.STUDENTRELATIONCONSTITUENCYTYPES on STUDENTRELATIONCONSTITUENCYSETTINGS.ID = STUDENTRELATIONCONSTITUENCYTYPES.STUDENTRELATIONCONSTITUENCYSETTINGSID
    left join dbo.RELATIONSHIPTYPECODE on STUDENTRELATIONCONSTITUENCYTYPES.RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODE.ID
    group by STUDENTRELATIONCONSTITUENCYSETTINGS.ID, CONSTITUENCYCODE.DESCRIPTION, STUDENTRELATIONCONSTITUENCYSETTINGS.RELATEDTOCODE 
    order by CONSTITUENCYCODE.DESCRIPTION