USP_DATALIST_MARRIAGEADDITIONALRELATIONSHIP

CREATE procedure dbo.USP_DATALIST_MARRIAGEADDITIONALRELATIONSHIP
as
set nocount on;

select 
  MARRIAGEADDITIONALRELATIONSHIP.ID,
  SPOUSERELATIONSHIPTYPECODE.DESCRIPTION SPOUSERELATION,
  RELATIONSHIPTYPECODE.DESCRIPTION RELATION,
  (select 
     dbo.UDA_BUILDLIST(RELATIONSHIPTYPECODE.DESCRIPTION)
   from dbo.MARRIAGEADDITIONALRELATIONSHIPASSOCIATION
   inner join dbo.RELATIONSHIPTYPECODE
     on RELATIONSHIPTYPECODE.ID = MARRIAGEADDITIONALRELATIONSHIPASSOCIATION.RELATIONSHIPTYPECODEID
   where MARRIAGEADDITIONALRELATIONSHIPASSOCIATION.MARRIAGEADDITIONALRELATIONSHIPID = MARRIAGEADDITIONALRELATIONSHIP.ID
  ) AS ASSOCIATIONS
from dbo.MARRIAGEADDITIONALRELATIONSHIP
inner join dbo.RELATIONSHIPTYPECODE SPOUSERELATIONSHIPTYPECODE
  on SPOUSERELATIONSHIPTYPECODE.ID = MARRIAGEADDITIONALRELATIONSHIP.SPOUSERELATIONSHIPTYPECODEID
inner join dbo.RELATIONSHIPTYPECODE 
  on RELATIONSHIPTYPECODE.ID = MARRIAGEADDITIONALRELATIONSHIP.RELATIONSHIPTYPECODEID
order by RELATIONSHIPTYPECODE.DESCRIPTION