Let’s say that you’ve built a group and that you then want to run a SQL query on the members of that group, perhaps to see who has responded to a campaign.

Groups are held in the contgrps table, and it’s not that hard to build a query on them.

For instance, the following query will show the basic contact information for all of the group member records.

Just change the name of the group owner and the group name in the last two lines to target the group that you want to show.


select contact1.accountno, company, contact, city, zip
from contact1, contgrps as cg1, contgrps as cg2
where cg2.userid=cg1.recid and contact1.accountno=cg2.accountno and
cg1.userid='your user id' and
cg1.ref='the group name'


In the query, contgrps is linking to itself as two aliases: cg1 and cg2. This is because the header entry (cg1) links to each of the member entries (cg2). Each member entry gives us the GoldMine accountno which we can then use to interrogate the associated records.

Returning the accountno in a result set is always a good idea because, then, clicking on any result line will automatically pull up the relevant record in the record tab.

The following example extends this principle and adds in information from the conthist table:


select contact1.accountno, company, contact from
contgrps as cg1, contgrps as cg2, contact1, conthist
where
cg2.userid=cg1.recid and
contact1.accountno=cg2.accountno and
conthist.accountno=cg2.accountno and
cg1.userid='your user id' and
cg1.ref='the group name' and
conthist.resultcode='ABC'