SELECT
  AT.ProductCategory,
  AT.AttendanceTypeID,
  TRIM(AT.AppointmentType) AS AppointmentType,
  X.SubType
FROM
  taProducts AS PR,
  tbAppointmentTypes AS AT
    LEFT JOIN taAttendanceExclusion AS AE ON ((AE.ProductID = @nProductID) AND (AE.AttendanceTypeID = AT.AttendanceTypeID))
    LEFT JOIN (
      SELECT
        AttendanceTypeID,
        CONCAT('[', GROUP_CONCAT('{"SubTypeID":', SubTypeID, ',"SubAppointmentType":"', TRIM(SubAppointmentType), '"}'), ']') AS SubType
      FROM
        taAppointmentSubType
      WHERE
        (SubAppointmentType IS NOT NULL) AND
        (TRIM(SubAppointmentType) <> '')
      GROUP BY
        AttendanceTypeID
    ) AS X ON (X.AttendanceTypeID = AT.AttendanceTypeID)
WHERE
  (PR.ProductID = @nProductID) AND
  (AT.ProductCategory = PR.ProductCategory) AND
  (AT.IsService = @nIsService) AND
  (AT.IsActive = 1) AND
  (TRIM(AT.AppointmentType) <> '') AND
  ((AE.ProductID IS NULL) OR (AT.AlwaysAllowed = 1))
ORDER BY
  AT.ProductCategory,
  AT.AppointmentType