CODEWITHSUNDEEP
sqlsql-serveremail
Fred Roven
Fred Roven

Reputation: 21

sql for primary email only

Using Goldmine 2014 on Windows 7 with SQL server 2008R

I have read dozens of responses about how to eliminate duplicate query results and either has not worked or in most cases just did not understand. Thanks for any help.

When I run the following query I get duplicate lines from the same contact record. I would like just the primary email returned. I think this addition AND cs.zip LIKE '_1%' might work but have not been able to integrate correctly. get The multi-part identifier "cs.zip" could not be bound. Tried DISTINCT also but no luck.

select  c1.dear
       ,c1.lastname
       ,c1.mergecodes
       ,c1.accountno
       ,EM.email 
from contact1 c1 
left join (select cs.accountno, cs.contsupref +isnull(cs.address1,'') Email 
           from contsupp cs 
           where cs.rectype='P' 
           and cs.contact = 'E-mail Address') as EM 
on EM.accountno = c1.accountno 
where (C1.Accountno > 'B40915000' 
      AND (C1.Mergecodes = '' OR C1.Mergecodes IS NULL) 
      AND (C1.Lastname <> '' OR C1.Lastname IS NOT NULL) 
      AND (C1.Dear <> '' OR C1.Dear IS NOT NULL)) 
      AND C1.ACCOUNTNO IN (SELECT DISTINCT ACCOUNTNO 
                           FROM CONTSUPP 
                           WHERE CONTSUPP.RECTYPE = 'P' 
                             AND CONTSUPP.CONTACT = 'E-mail Address') 
ORDER BY C1.accountno

Upvotes: 1

Views: 86

Answers (1)

M.Ali
M.Ali

Reputation: 69594

You can use ROW_NUMBER() function to get distinct emails.

also use Exists operator instead of using IN operator,

Even if you use IN operator you do not need to use Distinct inside you sub-query for IN operator. 

select  c1.dear
       ,c1.lastname
       ,c1.mergecodes
       ,c1.accountno
       ,EM.email 
from contact1 c1 
left join (select cs.accountno, cs.contsupref + isnull(cs.address1,'') Email 
                  ,ROW_NUMBER() OVER (PARTITION BY cs.accountno ORDER BY cs.accountno) rn
           from contsupp cs 
           where cs.rectype = 'P' 
           and cs.contact = 'E-mail Address') as EM 
on EM.accountno = c1.accountno AND EM.rn = 1
where  C1.Accountno > 'B40915000' 
  AND (C1.Mergecodes = '' OR C1.Mergecodes IS NULL) 
  AND (C1.Lastname <> ''  OR C1.Lastname IS NOT NULL) 
  AND (C1.Dear <> ''      OR C1.Dear IS NOT NULL)
  AND EXISTS   (SELECT 1 
                FROM CONTSUPP
                WHERE C1.ACCOUNTNO = CONTSUPP.ACCOUNTNO
                 AND CONTSUPP.RECTYPE = 'P'
                 AND CONTSUPP.CONTACT = 'E-mail Address')
ORDER BY C1.accountno

Upvotes: 1

Related Questions