UFN_RELATIONSHIPSBYMARRIAGE

CREATE function dbo.UFN_RELATIONSHIPSBYMARRIAGE
(
  @PARENTID uniqueidentifier,
  @CHILDID uniqueidentifier
)
returns @RESULT table
(
  ID uniqueidentifier,
  RECIPROCALCONSTITUENTID uniqueidentifier,
  RECIPROCALTYPECODEID uniqueidentifier,
  RELATIONSHIPCONSTITUENTID uniqueidentifier,
  RELATIONSHIPTYPECODEID uniqueidentifier,
  MARRIAGERELATIONSHIP bit
)
as
begin
  declare @SPOUSEID uniqueidentifier;
    select
        @SPOUSEID = RELATIONSHIP.RECIPROCALCONSTITUENTID
    from dbo.CONSTITUENT CONSTIT
    left join dbo.RELATIONSHIP
    on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTIT.ID and RELATIONSHIP.ISSPOUSE = 1
    left join dbo.CONSTITUENT SPOUSE
      on RELATIONSHIP.RECIPROCALCONSTITUENTID = SPOUSE.ID
    where CONSTIT.ID = @PARENTID

  insert into @RESULT
    --from children 

    SELECT 
      RELATIONSHIP.ID,
      RELATIONSHIP.RECIPROCALCONSTITUENTID,
      RELATIONSHIP.RECIPROCALTYPECODEID,
      RELATIONSHIP.RELATIONSHIPCONSTITUENTID,
      RELATIONSHIP.RELATIONSHIPTYPECODEID,
      0 MARRIAGERELATIONSHIP
    FROM dbo.RELATIONSHIP 
    inner join dbo.RELATIONSHIPBYMARRIAGE 
      on RELATIONSHIPBYMARRIAGE.RELATIONSHIPID = RELATIONSHIP.ID
    inner join dbo.RELATIONSHIP MARRIAGE
      on RELATIONSHIPBYMARRIAGE.MARRIAGERELATIONSHIPID = MARRIAGE.ID
    where 
      MARRIAGE.RELATIONSHIPCONSTITUENTID in (@SPOUSEID, @PARENTID) and 
      RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CHILDID AND 
      @CHILDID not in (@SPOUSEID, @PARENTID) and
      (
        (RELATIONSHIP.RECIPROCALCONSTITUENTID = @SPOUSEID OR --linked to spouse

         RELATIONSHIP.RECIPROCALCONSTITUENTID in --linked to sibling type relationships with spouse

         (select RELATIONSHIPCONSTITUENTID 
           FROM dbo.RELATIONSHIP 
           where RECIPROCALCONSTITUENTID = @SPOUSEID)
        ) 
        or
        (RELATIONSHIP.RECIPROCALCONSTITUENTID = @PARENTID OR --linked to parent

         RELATIONSHIP.RECIPROCALCONSTITUENTID in --linked to sibling type relationships with parent

         (select RELATIONSHIPCONSTITUENTID 
           FROM dbo.RELATIONSHIP 
           where RECIPROCALCONSTITUENTID = @PARENTID)
        )
      )
    union
    --marriage

    SELECT 
      RELATIONSHIP.ID,
      RELATIONSHIP.RECIPROCALCONSTITUENTID,
      RELATIONSHIP.RECIPROCALTYPECODEID,
      RELATIONSHIP.RELATIONSHIPCONSTITUENTID,
      RELATIONSHIP.RELATIONSHIPTYPECODEID,
      case when RELATIONSHIP.ISSPOUSE = 1 then 1 else 0 end MARRIAGERELATIONSHIP
    FROM dbo.RELATIONSHIP 
    where 
      (
        RELATIONSHIP.RECIPROCALCONSTITUENTID = @PARENTID and 
        RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @SPOUSEID and
        @CHILDID = @SPOUSEID
      )
      or
      (
        RELATIONSHIP.RECIPROCALCONSTITUENTID =  @SPOUSEID and 
        RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @PARENTID and
        @CHILDID = @PARENTID
      )
    union
    --from parents

    SELECT 
      RELATIONSHIP.ID,
      RELATIONSHIP.RECIPROCALCONSTITUENTID,
      RELATIONSHIP.RECIPROCALTYPECODEID,
      RELATIONSHIP.RELATIONSHIPCONSTITUENTID,
      RELATIONSHIP.RELATIONSHIPTYPECODEID,
      0 MARRIAGERELATIONSHIP
    FROM dbo.RELATIONSHIP 
    inner join dbo.RELATIONSHIPBYMARRIAGE
      on RELATIONSHIPBYMARRIAGE.RELATIONSHIPID = RELATIONSHIP.ID
    where 
      RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CHILDID and
      (
        ((@CHILDID = @SPOUSEID) and
        RELATIONSHIP.RECIPROCALCONSTITUENTID in 
        (select RELATIONSHIPCONSTITUENTID 
          FROM dbo.RELATIONSHIP 
          where RECIPROCALCONSTITUENTID = @PARENTID))
        or
        ((@CHILDID = @PARENTID) and
        RELATIONSHIP.RECIPROCALCONSTITUENTID in 
        (select RELATIONSHIPCONSTITUENTID 
          FROM dbo.RELATIONSHIP 
          where RECIPROCALCONSTITUENTID = @SPOUSEID))
      )
  return;
end