USP_BBNC_GETTEAMFUNDRAISINGTEAMS

            create procedure dbo.USP_BBNC_GETTEAMFUNDRAISINGTEAMS
            (
                @APPEALMAPID int
            )
            as
                set nocount on;

                select
                    [TEAMFUNDRAISINGTEAMMAP].[ID] as [BBNCID],
                    [TEAMFUNDRAISINGTEAM].[NAME],
                    [TEAMFUNDRAISINGTEAM].[GOAL],
                    coalesce
                    (
                        (
                            select top 1
                                [CONSTITUENT].[SEQUENCEID]
                            from
                                dbo.TEAMFUNDRAISINGTEAMCAPTAIN
                                inner join dbo.CONSTITUENT on [TEAMFUNDRAISINGTEAMCAPTAIN].[CONSTITUENTID] = [CONSTITUENT].[ID]
                            where
                                [TEAMFUNDRAISINGTEAMCAPTAIN].[TEAMFUNDRAISINGTEAMID] = [TEAMFUNDRAISINGTEAM].[ID]
                            order by
                                [TEAMFUNDRAISINGTEAMCAPTAIN].[DATECHANGED]
                        ),
                        0
                    ) as [FIRSTCAPTAINCONSTITUENTBBNCID],
                    coalesce([PARENTTEAMFUNDRAISINGTEAMMAP].[ID], 0) as [PARENTTEAMBBNCID]
                from
                    dbo.BBNCAPPEALIDMAP as [APPEALMAP]
                    inner join dbo.TEAMFUNDRAISINGTEAM on [APPEALMAP].[APPEALID] = [TEAMFUNDRAISINGTEAM].[APPEALID]
                    inner join dbo.BBNCTEAMFUNDRAISINGTEAMIDMAP as [TEAMFUNDRAISINGTEAMMAP] on [TEAMFUNDRAISINGTEAM].[ID] = [TEAMFUNDRAISINGTEAMMAP].[TEAMFUNDRAISINGTEAMID]
                    left join dbo.BBNCTEAMFUNDRAISINGTEAMIDMAP as [PARENTTEAMFUNDRAISINGTEAMMAP] on [TEAMFUNDRAISINGTEAM].[PARENTTEAMID] = [PARENTTEAMFUNDRAISINGTEAMMAP].[TEAMFUNDRAISINGTEAMID]
                where
                    [APPEALMAP].[ID] = @APPEALMAPID
                order by
                    [TEAMFUNDRAISINGTEAM].[NAME];