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

Question

I'm trying to find a way to list the Accounts to which a Contact is linked in a Sub-Panel of the Contact Record . 
 When linking a Contact to an Account, the Contact appears in the Accounts Sub-Panel for that Account, and the Account field in the Contact Record is updated to show that link. 
 The "problem" we have is that when a given Contact is the Purchasing Authority for 5 to 15 locations of a Customer, the Contact Record contains the Account link for the last time the contact was linked. 
 I'm thinking I may need to create a new Many-to-Many Relationship for Contact-Accounts, but I'm fearful that doing that would disrupt the standard SugarCRM design. 
 How do I see all of the accounts to which a contact is linked?

Bud Hartley · Accepted Answer

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

Bud Hartley · Answer

Happy to help!

Bud Hartley · Answer

Here you go. With account name and sales_territory_code_c 
 select accounts.name,
contacts.first_name, 
contacts.last_name, 
contacts.phone_work, 
email_addresses.email_address, 
contacts_cstm.sales_territory_code_c
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
and contacts.id = 'b1c20366-c320-11e7-957e-001a4a160206'