Additional Contacts with E-Mail Addresses - SQL

[sardine53]

We are using the Premium Edition of GoldMine and I need the Additional Contact information along with their Email addresses. Not every contact has an email address so I need the contact even when they don't have an email.

This query would be perfect if it included the email:

select contact1.company, contsupp.contact, contsupp.address1, contsupp.address2, contsupp.city, contsupp.state, contsupp.zip
FROM contsupp, contact1
WHERE contsupp.accountno=contact1.accountno and
contsupp.rectype='c'and
contsupp.mergecodes like '%JL%'

I realize the email addresses also reside in the Contsupp table (rectype='P' and Contact='E-mail Address') but don't know how to link this in SQL.

I have also read there maybe a problem with a long email and need to allow for that.

Any help would really be appreciated! Tek-Tips has always come though w/Crystal and I'm hoping the same goes for GoldMine.

Thanks!

- Jackie

 

[DJHunt]

Give this woman tried big guy:

select AccountNo,
Company,
Contact,
Address1,
Address2,
City,
State,
Zip,
Phone1,
(select top 1 contsupref+address1
from contsupp as cs
where cs.accountno = c1.accountno
and contact = 'E-MAIL ADDRESS'
and zip like '_1__'
) as Email
from contact1 as c1
Union select ContSupp.AccountNo,
ContSupp.Contact as Company,
ContSupp.Contact,
ContSupp.Address1,
ContSupp.Address2,
ContSupp.City,
ContSupp.State,
ContSupp.Zip,
ContSupp.Phone,
CS2.ContSupRef+CS2.Address1 AS Email
FROM Contact1, ContSupp, ContSupp CS2
WHERE Contact1.AccountNo=ContSupp.Accountno
and ContSupp.RecID = CS2.LinkAcct
AND ContSupp.RecType='C'
order by Company

DJ Hunt
Phone: (978)2-3333
WebSite:

http://www.DJHunt.US

- The Hacker's Guide to GoldMine Premium
- One-on-One GoldMine Technical Support ( Fee Based )

http://www.DJHunt.US/Default.html

 

[sardine53]

DJ,

Thanks for you prompt response.

It's almost there but I need 2 modifications:

- We only want Additional Contacts except we also need the Company name
- We need to limit the list by contsupp.mergecodes like '%JL%'

Thanks again,

Jackie

 

[sardine53]

Please disregard - I figured it out. Thanks for all your help!

 

[DJHunt]

We can only give you the knowledge to assist you in completing your task. It is up to you to use that knowledge in the best way that you can to meet your needs.

DJ Hunt
Phone: (978)2-3333
WebSite:

http://www.DJHunt.US

- The Hacker's Guide to GoldMine Premium
- One-on-One GoldMine Technical Support ( Fee Based )

http://www.DJHunt.US/Default.html