SELECT
  ConcernRole.concernRoleName,
  ConcernRole.primaryAlternateID,
  Address.addressID,
  Address.addressData,
  WebAddress.webAddress,
  EmailAddress.emailAddress,
  Employer.industryType,
  Employer.numberPermanentStaff,
  Employer.numberCasualStaff,
  Employer.businessDesc,
  RelatedCompaniesResult.companyCount
INTO
  :name,
  :primaryAlternateID,
  :addressID,
  :addressData,
  :webAddress,
  :emailAddress,
  :industryType,
  :numPermanentStaff,
  :numCasualStaff,
  :businessDescription,
  :numRelatedCompanies
FROM
  ConcernRole 
  
  LEFT OUTER JOIN
  
  Employer
  ON (ConcernRole.concernRoleID = Employer.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 
AND ConcernRoleRelationship.concernRoleType <> :concernRoleType AND 
ConcernRoleRelationship.relationshipType <> :concernRoleRelationshipType
  GROUP
    BY ConcernRoleRelationship.concernRoleID 
  ) RelatedCompaniesResult 
  ON RelatedCompaniesResult.concernRoleID = ConcernRole.concernRoleID 
WHERE ConcernRole.concernRoleID = :concernRoleID