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.