Contact linked to multiple accounts - How do I see all of the accounts to which a contact is linked?

Hi Patrick and Francesca,

While looking into this, one of the Sales Managers said he'd be happy with a report that could be sent to the territory reps and Customer Service.

The standard reports module doesn't accommodate pulling more than the related account, and I don't have a lot of experience with Advanced Reports.

What I'd like to pull is a report that would show the sales territory, contact name, office phone, email, and account names, and a way to know which of the account is the one in the related account field.

I'll start working on this, but if you wrote the SQL to create the report I'd be very thankful!

I don't know where your sales territory is stored but this should give you a start.

Here I am only pulling the primary email address, and only contacts that have an email address

select contacts.first_name, contacts.last_name, contacts.phone_work, email_address as primary_email_address
from contacts
join accounts_contacts on contacts.id = accounts_contacts.contact_id 
  and accounts_contacts.deleted = 0
join accounts on accounts.id = accounts_contacts.account_id 
  and accounts.deleted = 0
join email_addr_bean_rel on email_addr_bean_rel.bean_id = contacts.id 
  and email_addr_bean_rel.bean_module = 'Contacts' 
  and email_addr_bean_rel.deleted = 0 
  and email_addr_bean_rel.primary_address = 1
join email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id
where
contacts.deleted = 0
and contacts.id = 'b1c20366-c320-11e7-957e-001a4a160206'

This will get you contacts even if they don't have an email address (outer join), and all the email addresses if any (I removed the primary email flag check in the email_addr_bean_rel join condition).

select contacts.first_name, contacts.last_name, contacts.phone_work, email_address
from contacts
join accounts_contacts on contacts.id = accounts_contacts.contact_id 
  and accounts_contacts.deleted = 0
join accounts on accounts.id = accounts_contacts.account_id 
  and accounts.deleted = 0
left outer join email_addr_bean_rel on email_addr_bean_rel.bean_id = contacts.id 
  and email_addr_bean_rel.bean_module = 'Contacts' 
  and email_addr_bean_rel.deleted = 0 
left outer join email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id
where
contacts.deleted = 0
and contacts.id = 'b1c20366-c320-11e7-957e-001a4a160206'

You will need to join in custom tables for the contact and account if you need custom fields in your result set or filters.

Hope this helps,
Francesca

WOW Francesca! This is getting me close to what's needed.  I changed the Contact ID to one that I know appears in multiple Account records and it pulled all four records...  The Account Name is needed, and the custom field in the contact record is "sales_territory_code_c".  I'm working on this to see if I can get it finished.  THANK YOU! 

If you want ALL contacts with more than one account, this will count the number of accounts for each contact, return only the ones with more than one (having account_count > 1)

It also groups the account names and email addresses into one field for each contact so you should get only one line per contact.

You will need to review your results, make sure it's working for your scenarios, my instance is too customized to test thoroughly.

select 
group_concat(accounts.name SEPARATOR '; ') as account_names_semicolon_separated,
contacts.first_name, 
contacts.last_name, 
contacts.phone_work, 
group_concat(distinct(email_addresses.email_address) SEPARATOR '; ') as email_addresses_semicolon_separated, 
contacts_cstm.sales_territory_code_c,
count(distinct(accounts.id)) account_count
from contacts
join contacts_cstm on contacts_cstm.id_c = contacts.id
join accounts_contacts on contacts.id = accounts_contacts.contact_id 
  and accounts_contacts.deleted = 0
join accounts on accounts.id = accounts_contacts.account_id 
  and accounts.deleted = 0
left outer join email_addr_bean_rel on email_addr_bean_rel.bean_id = contacts.id 
  and email_addr_bean_rel.bean_module = 'Contacts' 
  and email_addr_bean_rel.deleted = 0 
left outer join email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id
where
contacts.deleted = 0
group by contacts.id 
having account_count > 1

Let me know how it goes.

Francesca

WOW Again Francesca! You are SO VERY talented and helpful.  I'll be working on this later today - the SQL is VERY close to exactly what I need.

Happy to help!

YES!  This is it!  THANK YOU Francesca! You have made the Sales VPs VERY happy!

YES!  This is it!  THANK YOU Francesca! You have made the Sales VPs VERY happy!