SELECT
ParticipantAttendee.participantAttendees,
UserAttendee.userAttendees,
ServiceSupplier.supplierAttendees,
ThirdPartyAttendee.thirdPartyAttendees

INTO
:participantAttendeeCount,
:userAttendeeCount,
:serviceSupplierAttendeeCount,
:thirdPartyAttendeeCount

FROM
Hearing

LEFT OUTER JOIN (
  SELECT Count(*) AS participantAttendees,
  Hearing.hearingID
FROM
  CaseParticipantRole,
  Hearing
WHERE
  CaseParticipantRole.caseID = Hearing.caseID AND
  CaseParticipantRole.typeCode <> :hearingOfficialRoleType AND
  CaseParticipantRole.recordStatus = :normalRecordStatus
GROUP BY Hearing.hearingID
  ) ParticipantAttendee
ON (ParticipantAttendee.hearingID = Hearing.hearingID)

LEFT OUTER JOIN (
  SELECT Count(*) AS userAttendees,
  Hearing.hearingID
FROM 
  Hearing, 
  HearingUserRole, 
  CaseUserRole
WHERE
  CaseUserRole.caseID = Hearing.caseID AND
  HearingUserRole.hearingID = Hearing.hearingID AND
  HearingUserRole.caseUserRoleID = CaseUserRole.caseUserRoleID AND 
  CaseUserRole.typeCode <> :hearingOfficialUserRoleType AND
  CaseUserRole.recordStatus = :normalRecordStatus
GROUP BY Hearing.hearingID
) UserAttendee
ON (UserAttendee.hearingID = Hearing.hearingID)

LEFT OUTER JOIN (
  SELECT Count(*) AS supplierAttendees,
  Hearing.hearingID
FROM 
  HearingServiceSupplier,
  CaseUserRole,
  Hearing
WHERE 
  HearingServiceSupplier.hearingID = Hearing.hearingID AND
  HearingServiceSupplier.supplierType = :userServiceSupplierCode AND
  HearingServiceSupplier.supplierLinkID = CaseUserRole.caseUserRoleID AND
  CaseUserRole.recordStatus = :normalRecordStatus

GROUP BY Hearing.hearingID
) ServiceSupplier
ON (ServiceSupplier.hearingID = Hearing.hearingID)

LEFT OUTER JOIN (
  SELECT Count(*) AS thirdPartyAttendees,
  Hearing.hearingID
FROM
  ThirdParty,
  Appeal,
  Hearing
WHERE 
  Hearing.caseID = Appeal.caseID AND 
  ThirdParty.appealID = Appeal.appealID AND 
  ThirdParty.toDate IS NULL 
GROUP BY Hearing.hearingID
) ThirdPartyAttendee
ON (ThirdPartyAttendee.hearingID = Hearing.hearingID)

WHERE
Hearing.hearingID = :hearingID