AnsweredAssumed Answered

Not able to retrieve email address from contact who created a case

Question asked by mark benfield on Jul 8, 2014
Latest reply on Jul 11, 2014 by mark benfield
Hi,  I need to return the email address of the contact who raised the call along with other details. I can not achieve this. This is the current code.
I can get the first name and last name from contacts, but not the email address from the email_addresses. I have seen suggestions to use the email_addr_bean_rel. But I can't get this to work at all.

SELECTcc.contact_id,
   c.id AS CRMCaseID,
   c.case_number AS CaseNumber,
   c.name AS Name,
   a.name AS OrganisationName,
   c.date_entered AS Created,
   uc.user_name AS CreatedBy,
   c.date_modified AS Modified,
   um.user_name AS ModifiedBy,
   ua.user_name AS AssignedTo,
   c.status AS Status,
   ca.area_c AS Area,
   ca.classification_type_c AS ClassificationType,
   ca.call_location_c AS CallLocation,
   ca.resolver_group_c AS ResolverGroup,
   ca.connect_functional_area_c AS ConnectFunctionalArea,
   ca.case_type_c AS CaseType,
   CONCAT(um.first_name, ' ', um.last_name) AS Name
FROM cases c
JOIN contacts_cases cc
ON cc.case_id = c.id
JOIN contacts
ON cc.contact_id = contacts.id
JOIN cases_cstm ca
ON c.id = ca.id_c
JOIN users um
ON c.modified_user_id = um.id
JOIN users uc
ON c.created_by = uc.id
JOIN users ua
ON c.assigned_user_id = ua.id
JOIN accounts a
ON a.id = c.account_id
WHERE c.case_number = 63653
AND c.deleted = 0
GROUP BY c.case_number

Outcomes