Categories

Follow our news:

Follow canonburysvcs on Twitter Follow Canonbury Services on Facebook Follow Canonbury Services' news by RSS Follow Canonbury Services' news by Atom Follow Canonbury Services' news by email

NEWS & TECH BLOG

Extract a list of e-mail addresses

12/03/2013 – in SQL queries

E-mail addresses are held in the contsupp table. The address itself is held in the contsupref field but, if it’s any longer than 35 characters then the overflow is held in contsupp.address1.

The address2 field holds the contact name to whom the e-mail address belongs, although sometimes – not always! – this is blank for primary contacts.

Lets start by extracting all email addresses, with their associated names, as well as the company name and primary contact name from contact1. To make it more interesting I’ve added a clause to limit the results to ‘warm’ and ‘hot’ prospects using key2:


select
company, contact1.contact, contsupp.address2, key2, coalesce(contsupref+contsupp.address1, contsupref)
from
contact1, contact2, contsupp
where
contact1.accountno=contact2.accountno and contact1.accountno=contsupp.accountno
and
(key2='hot' or key2='warm')
and
contsupp.contact='e-mail address'
order by company, contsupp.address2

If you want to limit the selection to just primary addresses then you need to add the following to the selection:


and
contsupp.zip like '_1%'

Here’s the finished result. I’ve kept in the address2 column because that’s a useful sanity check; the primary contact may have been changed at some point without the primary email address being updated, and this will help you spot those instances:


select
company, contact1.contact, contsupp.address2, key2, coalesce(contsupref+contsupp.address1, contsupref)
from
contact1, contact2, contsupp
where
contact1.accountno=contact2.accountno and contact1.accountno=contsupp.accountno
and
(key2='hot' or key2='warm')
and
contsupp.contact='e-mail address'
and
contsupp.zip like '_1%'
order by company

I’m sorting by company but you could, of course, sort by contact1.contact if you prefer.

Share