USP_BBNC_CONSTITRELATEDINDIVIDUALSDATA

            CREATE procedure dbo.USP_BBNC_CONSTITRELATEDINDIVIDUALSDATA(@ID int)
            as
                set nocount on;

                select
                    RECIPROCALCONSTITUENT.SEQUENCEID [MAPID],
                    RECIPROCALCONSTITUENT.NAME,
                    cast(case when DECEASEDCONSTITUENT.ID is null then 0 else 1 end as bit) [DECEASED]
                from
                    dbo.CONSTITUENT
                inner join
                    dbo.RELATIONSHIP on CONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                inner join
                    dbo.CONSTITUENT as RECIPROCALCONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = RECIPROCALCONSTITUENT.ID
                left join
                    dbo.DECEASEDCONSTITUENT on RECIPROCALCONSTITUENT.ID = DECEASEDCONSTITUENT.ID
                where 
                    CONSTITUENT.SEQUENCEID = @ID
                    and RECIPROCALCONSTITUENT.ISORGANIZATION = 0
                    and RECIPROCALCONSTITUENT.ISGROUP = 0; -- JohnLu 2007/08/20 Guarantee that groups are not returned in the query