UFN_SITEID_MAPFROM_CONSTITUENTORACQUISITIONLIST

CREATE function dbo.[UFN_SITEID_MAPFROM_CONSTITUENTORACQUISITIONLIST]
(  
  @CONSTITUENTID uniqueidentifier  
)   
returns table
as  
return
(
    /* This statement will return null if there are no sites */
    select
      [SITES].[SITEID]
    from (select @CONSTITUENTID as [CONSTITUENTID]) as [CONSTITUENT]
    left join
      (
        /* Existing constituent sites */
        select 
          [CONSTITUENTID] as [CONSTITUENTID],
          [SITEID]
        from dbo.[CONSTITUENTSITE]
        where [CONSTITUENTID] = @CONSTITUENTID

        union

        /* Acquisition list member sites (they do not yet exist as real constituents) */
        select 
          [MKTFINDERNUMBERCONSTITUENTID] as [CONSTITUENTID],
          [SITEID]
        from dbo.[MKTFINDERNUMBERCONSTITUENTSITE]
        where [MKTFINDERNUMBERCONSTITUENTSITE].[MKTFINDERNUMBERCONSTITUENTID] = @CONSTITUENTID
      ) as [SITES] on [SITES].[CONSTITUENTID] = [CONSTITUENT].[CONSTITUENTID]

)