SQL Statement for e-mail

[ddeegan]

Hello

I can't figure out the following

Write a SQL query for contact1, conatct2, & contsupp to pull company info, user defined field, and e-mail address.

I want to include all records from contact1 & contact2 EVEN IF the record doesn't have an e-mail.

So far, all I have is what is below, but it only returns records that have a record in contsupp.

I know i have to do a type of join, but the syntax is driving me nuts and I'm hoping it is common enough that someone could post it. I also need it to work in the GM SQL window.

Thanks in advance
Dave


select ulcredit as 'Listing Credit',
contact1.company, contact1.contact, contact1.key1 as 'ASI #' ,
contact1.address1 as 'Address1',
contact1.address2 as 'Address2',
contact1.city,
contact1.state,
contact1.zip,
phone1 as 'Phone',
contact1.fax as 'Fax',
contsupref as 'E-mail'
from contact1, contact2, contsupp
where
contact1.accountno=contact2.accountno
and
contsupp.accountno=contact1.accountno
and
contact2.udlisted > '1/01/04'
and
contact2.udlisted < '1/30/04'
and (contsupp.contsupref is null
or
(contsupp.contsupref is not null and contsupp.Contact like 'E-mail Address%' and contsupp.zip like '_1%')
)
order by contact1.company

 

[M8KWR]

Just a quick note... unsure on the sql side as i'm not that great at it, but the email address is actually stored within 2 fields in the contsupp table.

If the number of characters goes over 30 then the rest of the email address is put into the "address1" field.

You could try a forum called Contact Review url

http://www.contactreview.com/mb/ubbthreads.php

This has some excellent people who are quick to respond to your issues.

Hope this helps

 

[philhege]

rtrim(c3.contsupref)+isnull(rtrim(c3.address1),'') as email,

is the expression I use. You have to do a LEFT OUTER JOIN on the contsupp table (referred in my example as c3) to get records without email addresses.

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.