UFN_SITEID_MAPFROM_CONSOLIDATEDLISTMEMBERID

create function dbo.[UFN_SITEID_MAPFROM_CONSOLIDATEDLISTMEMBERID]
(
  @CONSOLIDATEDID uniqueidentifier
)
returns table
as  
return
(
  select
    [CONSTITUENTSITE].[SITEID]
  from (select @CONSOLIDATEDID as [CONSTITUENTID]) as [T]
  left outer join dbo.[CONSTITUENTSITE] on [CONSTITUENTSITE].[CONSTITUENTID] = [T].[CONSTITUENTID]
  where exists (select top 1 1 from dbo.[CONSTITUENT] where [ID] = @CONSOLIDATEDID)
  union
  select
    [MKTSEGMENT].[SITEID]
  from (select @CONSOLIDATEDID as [SEGMENTLISTDATAID]) as [T]
  left outer join dbo.[MKTSEGMENTLISTDATA] on [MKTSEGMENTLISTDATA].[ID] = [T].[SEGMENTLISTDATAID]
  left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENTLISTDATA].[SEGMENTLISTID]
  left outer join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTLIST].[SEGMENTID]
  where exists (select top 1 1 from dbo.[MKTSEGMENTLISTDATA] where [ID] = @CONSOLIDATEDID)
  and not exists (select top 1 1 from dbo.[CONSTITUENT] where [ID] = @CONSOLIDATEDID)
)