USP_TRIBUTEEXTENSION_DATALIST

CREATE procedure dbo.USP_TRIBUTEEXTENSION_DATALIST
    @FROMCONSTITUENTID uniqueidentifier
as
    set nocount on;

--SELECT TOP 1 T.TRIBUTEEFIRSTNAME, T.TRIBUTEELASTNAME, T.TRIBUTEECITY, T.TRIBUTEEPOSTCODE,

--       T.TRIBUTEECOUNTRYID, C.DESCRIPTION AS COUNTRY,

--       T.TRIBUTEESTATEID, S.DESCRIPTION AS STATE,

--       '' AS TRIBUTEEEMAIL, '' AS TRIBUTEEADDRESS

--FROM dbo.TRIBUTEEXTENSION T

--LEFT OUTER JOIN COUNTRY C on C.ID = T.TRIBUTEECOUNTRYID

--LEFT OUTER JOIN STATE S ON S.ID = T.TRIBUTEESTATEID

--WHERE T.FROMCONSTITUENTID = @FROMCONSTITUENTID


SELECT TOP 1 
       ISNULL(CO.FIRSTNAME, T.TRIBUTEEFIRSTNAME) AS TRIBUTEEFIRSTNAME, ISNULL(CO.KEYNAME, T.TRIBUTEELASTNAME) AS TRIBUTEELASTNAME, 
       ISNULL(ADDR.CITY, T.TRIBUTEECITY) AS TRIBUTEECITY, ISNULL(ADDR.POSTCODE, T.TRIBUTEEPOSTCODE) AS TRIBUTEEPOSTCODE,
       ISNULL(ADDR.COUNTRYID, T.TRIBUTEECOUNTRYID) AS TRIBUTEECOUNTRYID, C2.DESCRIPTION AS TRIBUTEECOUNTRY,
       ISNULL(ADDR.STATEID, T.TRIBUTEESTATEID) AS TRIBUTEESTATEID, S2.DESCRIPTION AS TRIBUTEESTATE, 
       ISNULL(EADDR.EMAILADDRESS, T.TRIBUTEEEMAILADDRESS) AS TRIBUTEEEMAIL, ISNULL(ADDR.ADDRESSBLOCK, T.TRIBUTEEADDRESSBLOCK) AS TRIBUTEEADDRESS,
     TRI.TRIBUTEEID
FROM TRIBUTEEXTENSION T
INNER JOIN TRIBUTE TRI ON T.TRIBUTEID = TRI.ID
LEFT OUTER JOIN CONSTITUENT CO ON TRI.TRIBUTEEID = CO.ID 
LEFT OUTER JOIN ADDRESS ADDR ON TRI.TRIBUTEEID = ADDR.CONSTITUENTID AND ADDR.ISPRIMARY = 1
LEFT OUTER JOIN EMAILADDRESS EADDR ON TRI.TRIBUTEEID = EADDR.CONSTITUENTID AND EADDR.ISPRIMARY = 1
LEFT OUTER JOIN COUNTRY C2 ON C2.ID = T.TRIBUTEECOUNTRYID 
LEFT OUTER JOIN STATE S2 ON S2.ID = T.TRIBUTEESTATEID 
WHERE T.FROMCONSTITUENTID = @FROMCONSTITUENTID