It’s quite a common request to export a list of contacts with their associated email addresses. However, because of the way that GoldMine is structured this is actually harder than it might seem, particularly if you want a single list that contains both the primary and additional contacts.

Primary contacts are held in the contact1 table;
Additional contacts are held in ‘C’ records in the contsupp table;
Email addresses are held in ‘P’ records in the contsupp table (although not all ‘P’ records are email addresses!).

What you need to do is to knit all of those together, taking note of the fact that not all contacts will have email addresses.

Here’s a query that will do all of that:


select c1.company, p.contact, p.address1, p.address2, p.address3, p.city, p.county, p.postcode, p.phone, p.switch, p.mobile, p.email, p.primary_addr, p.email_mergecodes, c1.mergecodes, c1.ext3 as flag, c1.accountno from
(
select c.accountno, contact, address1, address2, address3, city, state as county, zip as postcode, phone1 as phone, phone2 as switch, phone3 as mobile, email, primary_addr, email_mergecodes
from contact1 as c
left outer join
(select accountno, coalesce(contsupref+address1,contsupref) as email, substring(zip, 2, 1) as primary_addr, mergecodes as email_mergecodes from contsupp where
rectype='p' and
contact='e-mail address' and
zip like '_1%') as cs_em on
c.accountno=cs_em.accountno

union

select c.accountno, contact, address1, address2, address3, city, state as county, zip as postcode, phone, '', '', email, primary_addr, email_mergecodes
from contsupp as c
left outer join
(select accountno, linkacct, coalesce(contsupref+address1,contsupref) as email, substring(zip, 2, 1) as primary_addr, mergecodes as email_mergecodes from contsupp where
rectype='p' and
contact='e-mail address') as cs_em
on c.recid=cs_em.linkacct
where c.rectype='c'
) as p

inner join contact1 as c1
on p.accountno=c1.accountno

order by c1.company, c1.accountno, p.primary_addr desc, p.contact


The inner join to contact1 extracts the company name, the primary mergecodes (more about this in a moment) and the ext3 field (which is frequently used as a flag. It could also pull any other contact1 fields that we want, such as the key fields.

The first subquery extracts the primary contact and the primary email address, if there is one; the second subquery extracts any additional contacts, if any, and their associated email addresses, if there are any. The ‘union’ joins them both together into the single list that we want.

A ‘1’ in the primary_address column shows which of the email addresses is marked as the primary. Sometimes none of them are, sometimes more than one is (this shouldn’t happen, but it’s not a perfect world).

Each email address has a mergecodes field associated with it, used for email marketing. There is also a snail-mail-mergecodes field that people often misuse: because the primary contact’s snail-mail-mergecodes field appears in top half of the record by default, it’s often used as a marketing code for the whole record. Recognising this fact, the query brings out that field against all of the contacts for that record.

The output is ordered by company name, then individual records within that company, then by primary address flag, and finally by contact name. So the owner of the primary address will be the first name listed within a record, then everyone else who has an email address, in name order, and finally those users without email addresses, in name order. Obviously you can change this to suit your own requirements.