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.contact, contact.title, coalesce(email.contsupref+email.address1,email.contsupref) from
contsupp as email, contsupp as contact where
contact.recid=email.linkacct and
email.contact='e-mail address' and
contact.rectype='c'


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.