SQL Tables

[Shairal]

Can anyone tell me if there is a SQL table where I can find email addresses that have opted out of a newsletter campaign?

Thanks in advance!

[crsoftware]

Hopefully, some of this helps. It depends on what version of Sugar you're using. If you use 5.x and you use the 'Opted out' check-box on the contact's edit screen for your newsletters, that info can be pulled from the email_addresses table.

SELECT
 email_address
FROM
 email_addresses
WHERE
 opt_out = 1

That would just give you a list of email addresses. If you want to associate contact names with these email addresses it becomes more complicated. You have to join to the contacts table using the email_addr_bean_rel table.

SELECT
 c.first_name,
 c.last_name,
 email.email_address
FROM
 contacts c
 JOIN email_addr_bean_rel bean ON (c.id = bean.bean_id AND bean.bean_module = 'Contacts')
 JOIN email_addresses email ON (bean.email_address_id = email.id)
WHERE
 email.opt_out = 1

The interesting thing about the second query is that it could return more results than the first query if two or more people have the same email address and have opted out. The first query will only show the opted out email address once, but the second will show it two or more times because of the association with the different contacts.

The first query could have returned...

bob@abc.com
mary@345.com
it_dept@xyz.com

...but the second could return...

Bob Smith bob@abc.com
Mary Jones mary@345.com
John Collins it_dept@xyz.com
Jeff Williams it_dept@xyz.com
George Smart it_dept@xyz.com

[Shairal]

Thank you so much for your assistance!!