SELECT
  ConcernRole.concernRoleName,
  ConcernRole.primaryAlternateID,
  Address.addressID,
  Address.addressData,
  WebAddress.webAddress,
  EmailAddress.emailAddress,
  ProspectEmployer.industryType,
  ProspectEmployer.numberPermanentStaff,
  ProspectEmployer.numberCasualStaff,
  ProspectEmployer.businessDesc,
  RelatedCompaniesResult.companyCount
INTO
  :name,
  :primaryAlternateID,
  :addressID,
  :addressData,
  :webAddress,
  :emailAddress,
  :industryType,
  :numPermanentStaff,
  :numCasualStaff,
  :businessDescription,
  :numRelatedCompanies
FROM
  ConcernRole 
  
  LEFT OUTER JOIN
  
  ProspectEmployer
  ON (ConcernRole.concernRoleID =  ProspectEmployer.concernRoleID)  
  
  LEFT OUTER JOIN
  
  Address 
  ON (ConcernRole.primaryAddressID = Address.addressID) 
  
  LEFT OUTER JOIN
  
  WebAddress 
  ON (ConcernRole.primaryWebAddressID = WebAddress.webAddressID) 
  
  LEFT OUTER JOIN
  
  EmailAddress 
  ON (ConcernRole.primaryEmailAddressID = EmailAddress.emailAddressID) 
  
  LEFT OUTER JOIN
  
  
  ( 
  SELECT
    ConcernRoleRelationship.concernRoleID,
    COUNT(ConcernRoleRelationship.concernRoleID) as companyCount 
  FROM
    ConcernRoleRelationship 
  WHERE ConcernRoleRelationship.concernRoleID = :concernRoleID 
  GROUP
    BY ConcernRoleRelationship.concernRoleID 
  ) RelatedCompaniesResult 
  ON RelatedCompaniesResult.concernRoleID = ConcernRole.concernRoleID 
WHERE ConcernRole.concernRoleID = :concernRoleID