SQL Statement for Contacts tab e-mail 2

[ddeegan]

Hello

Does anyone know the SQL statement to pull out the secondary contacts and their e-mail address? I know the contact and the e-mail address are in 2 seperate records, but how can I pull them out off Contsupp through a SQL statement?

Thanks
Dave

 

[MHPGuy]

This will work to just pull the contacts

select contact, contsupref, accountno from contsupp where contsupref like '%@%' Michael Phipps
Technical Business Analyst
Mercy Health Plans

 

[ddeegan]

Thanks Michael, but is it possible to run a statement that will pull the secondary contact name also? When you add a secondary contact, it places the contact info in one record with rectype = 'C' and the e-mail address with rectype = 'P'. The link seems to be between the contact field with rectype 'C' and the address2 field of the record with rectype='P'.

I wanted a result set that looked like the following. (All from contsupp)

Secondary Contact E-Mail
----------------------------------------
Joe Smith JSmith@aol.com
Bob Jones BJones@aol.com

I could create another table, populate the table w/e-mail addresses, then join the tables together. I was hoping there was a statement I could do and avoid creating other tables. I'm having trouble getting the syntax.

Thanks
Dave

 

[ddeegan]

Hello again, I answered my own question. I don't know why I didn't see it before.

To get the contact and e-mail address, the statement would be

select contsupref, address2 from contsupp where rectype='P'
and contsupref like '%@%'
and address2 is not null

... this may/may not be the most efficient way to write it. Thanks for all your help.

 

[MHPGuy]

Okay, try this...

SELECT address2, contsupref FROM CONTSUPP WHERE contsupp.rectype = "P"

to remove the blank names you can also do this

SELECT address2, contsupref FROM CONTSUPP WHERE contsupp.rectype = &quot;P&quot; and contsupp.address2 <> &quot;&quot; Michael Phipps
Technical Business Analyst
Mercy Health Plans