SELECT
  DISTINCT ConcernRole.concernRoleName,
  ConcernRole.primaryAlternateID,
  Address.addressData,
  WebAddress.webAddress,
  PhoneNumber.phoneNumberID,
  EmailAddress.emailAddress,
  ExternalParty.type,
  ExternalParty.verificationInd,
  ExternalPartyOfficeResult.officeCount
INTO
  :name,
  :primaryAlternateID,
  :addressData,
  :webAddress,
  :phoneNumberID,
  :emailAddress,
  :externalPartyType,
  :verificationInd,
  :relatedOffices 
FROM
  ConcernRole 
  
  LEFT OUTER JOIN
  
  ProductProvider 
  ON (ConcernRole.concernRoleID = ProductProvider.concernRoleID)
  
  LEFT OUTER JOIN
  
  Address 
  ON (ConcernRole.primaryAddressID = Address.addressID) 
  
  LEFT OUTER JOIN
  
  WebAddress 
  ON (ConcernRole.primaryWebAddressID = WebAddress.webAddressID) 
  
  LEFT OUTER JOIN
  
  PhoneNumber 
  ON (ConcernRole.primaryPhoneNumberID = PhoneNumber.phoneNumberID) 
  
  LEFT OUTER JOIN
  
  EmailAddress 
  ON (ConcernRole.primaryEmailAddressID = EmailAddress.emailAddressID) 
  
  LEFT OUTER JOIN
  
  ExternalParty 
  ON (ConcernRole.concernRoleID = ExternalParty.concernRoleID) 
  
  LEFT OUTER JOIN
  
  
  ( 
  SELECT
    ExternalPartyOffice.concernRoleID,
    COUNT(ExternalPartyOffice.externalPartyOfficeID) as officeCount 
  FROM
    ExternalPartyOffice 
  WHERE ExternalPartyOffice.concernRoleID = :concernRoleID 
  GROUP
    BY ExternalPartyOffice.concernRoleID 
  ) ExternalPartyOfficeResult 
  ON ExternalPartyOfficeResult.concernRoleID = ConcernRole.concernRoleID 
WHERE ConcernRole.concernRoleID = :concernRoleID