SELECT
  AT.ProductCategory,
  AT.AttendanceTypeID,
  TRIM(AT.AppointmentType) AS AppointmentType,
  X.SubType
FROM
  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
  (AT.IsService = @nIsService) AND
  (AT.IsActive = 1) AND
  (TRIM(AT.AppointmentType) <> '') AND
  ((AE.ProductID IS NULL) OR (AT.AlwaysAllowed = 1)) AND
  (AT.ProductCategory NOT IN ((
     SELECT
       PR1.ProductCategory
     FROM
       taClientProducts AS CP1
         INNER JOIN taProducts AS PR1 USING (ProductID)
         INNER JOIN taProductCategory AS PC1 USING (ProductCategory)
     WHERE
       (CP1.ClientID = @nClientID) AND
       (CP1.Status = 1) AND
       (PC1.CoverAllowed = 1)
  )))
ORDER BY
  AT.ProductCategory,
  AT.AppointmentType