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


Another query for extracting email addresses

30/10/2013 – in SQL queries

A customer phoned earlier and asked for a query that would extract the email addresses of additional contacts, plus their names and their job titles.

This involved an internal join on the contsupp table and demonstrates a couple of useful techniques, so I thought it worth sharing.

Here’s the script:

select contact.accountno,, contact.title, coalesce(email.contsupref+email.address1,email.contsupref) from
contsupp as email, contsupp as contact where
contact.recid=email.linkacct and'e-mail address' and

The magic comes from linking contsupp back onto itself by giving each of its two appearances an alias: in this case ’email’ and ‘contact’.

The records link together by the recid of the contact record appearing in the linkacct of the email record. That way, one person can link to multiple addresses.

The other useful thing is the coalesce statement. It’s a way of combining two fields that avoids the problems that arise should one of those fields contain a NULL value.