Advanced Reporting on Email Field in Accounts Module

Question

I am looking to create an Advanced Report but need some assistance with the SQL formula. 
 The Accounts Module allows you to add multiple email addresses, but with out of the box reporting it will only pull the primary email. I need it to pull each email listed. 
 Defined by: 
 Type=Current Customer 
 
 Display Columns: 
 Account Number 
 Account Name 
 Email Address 
 Can someone help bet me started in creating a query for this?

Brianna Schardt · Answer

My apologies if I'm going into obvious detail, I don't know your level of familiarity with Sugar's Studio and database table structure. 
 Here is a starting point: 
 All relationships between modules and email_addresses are via the email_addr_bean_rel table where there are three essential columns: 
 email_address_id (links to email_addresses) 
 bean_module (the name of the module the address is on, for example "Contacts", "Accounts" etc) 
 bean_id (the id of the record the email_address is on) 
 So your query will look something like: 
 select accounts.name, 
email_addresses.email_address,
accounts.account_type,
accounts_cstm.account_number_c
from accounts
join accounts_cstm on accounts_cstm.id_c = accounts.id
join email_addr_bean_rel on email_addr_bean_rel.bean_id = accounts.id 
 and email_addr_bean_rel.bean_module = 'Accounts' 
 and email_addr_bean_rel.deleted = 0
join email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id
 and email_addresses.deleted = 0
where accounts.deleted = 0
and account.account_type = 'CurrentCustomer' 
 Note that I added accounts_cstm which is the table where your custom fields for Accounts are stored. I am assuming the Account Number is a custom field. I am also assuming that your field name is account_number_c (this is a big assumption). 
 To see the name of the field from your instance, you can use Studio (Admin->Studio->Accounts->Fields) or open an account record view, right click on that field's label and "Inspect", in the "Elements" tab of your tools you will see something like: 
 data-name="the_name_of_your_field_here_c" 
 As for the "Current Customer" value, you will need to check what you are using for the actual value stored in the database: go to 
 Admin->Studio->Accounts->Fields->account_type 
 just below the "Drop down List" is an Edit button, click that and it will show you all the values and labels. 
 Use the value on the left, in bold, as the value for the filter. 
 
 Hope this helps you get what you need. 
 FrancescaS