USP_BBNC_CONSTITRELATEDINDIVIDUALSDATA_1_1

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

                select
                    BBNCRELATIONSHIPIDMAP.ID [RELATIONSHIPMAPID],
                    RECIPROCALCONSTITUENT.SEQUENCEID [RELATEDCONSTITUENTMAPID],
                    RECIPROCALCONSTITUENT.NAME,
                    cast(case when DECEASEDCONSTITUENT.ID is null then 0 else 1 end as bit) [DECEASED],
                    COUNTRY.DESCRIPTION as COUNTRYLONG,
                    COUNTRY.ABBREVIATION as COUNTRYSHORT,
                    ADDRESS.ADDRESSBLOCK,
                    ADDRESS.CITY,
                    STATE.ABBREVIATION AS STATE,
                    ADDRESS.POSTCODE,
                    dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID) as FORMATTEDADDRESS,
                    COUNTRY.COUNTRYADDRESSFORMATID
                from
                    dbo.CONSTITUENT
                inner join
                    dbo.RELATIONSHIP on CONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                inner join
                    dbo.BBNCRELATIONSHIPIDMAP on RELATIONSHIP.ID = BBNCRELATIONSHIPIDMAP.RELATIONSHIPID
                inner join
                    dbo.CONSTITUENT as RECIPROCALCONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = RECIPROCALCONSTITUENT.ID
                left join
                    dbo.DECEASEDCONSTITUENT on RECIPROCALCONSTITUENT.ID = DECEASEDCONSTITUENT.ID
                left join
                    dbo.ADDRESS on RECIPROCALCONSTITUENT.ID = ADDRESS.CONSTITUENTID
                left join
                    dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
                left join
                    dbo.STATE on ADDRESS.STATEID = STATE.ID
                where 
                    CONSTITUENT.SEQUENCEID = @ID
                    and RECIPROCALCONSTITUENT.ISORGANIZATION = 0
                    and RECIPROCALCONSTITUENT.ISGROUP = 0 -- JohnLu 2007/08/20 Guarantee returned relationships are not with groups

                    and 
                    (
                        ADDRESS.ISPRIMARY = 1
                        or
                        ADDRESS.ID is null
                    );