SELECT
  CP.ProductID,
  CP.ClientProductID,
  CONCAT(PR.ProductName, IF(PR1.ProductName IS NULL, '', CONCAT(' [child of (', PR1.ProductName, ')]'))) AS ProductName,
  Y.AllowedBranches,
  Z.AttendanceCount
FROM
  taClientProducts AS CP
    LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
    LEFT JOIN taProductCategory AS PC ON (PC.ProductCategory = PR.ProductCategory)
    LEFT JOIN taClientProducts AS CP1 ON (CP1.ClientProductID = CP.ParentClientProductID)
    LEFT JOIN taProducts AS PR1 ON (PR1.ProductID = CP1.ProductID)
    LEFT JOIN (

      SELECT
        AT.ProductCategory,
        COUNT(*) AS AttendanceCount
      FROM
        tbAppointmentTypes AS AT
      WHERE
        (AT.IsActive = 1) AND
        (AT.IsService = @nIsService) AND
        (TRIM(AT.AppointmentType) <> '')
      GROUP BY
        AT.ProductCategory

    ) AS X ON (X.ProductCategory = PR.ProductCategory)
    LEFT JOIN (

      SELECT
        BP.ClientProductID,
        GROUP_CONCAT(DISTINCT BP.ClientBranchID) AS AllowedBranches
      FROM
        taClientBranchProducts AS BP
          INNER JOIN taClientProducts AS CP2 ON (CP2.ClientProductID = BP.ClientProductID)
      WHERE
        (BP.Status = 1) AND
        (CP2.Status = 1) AND
        (CP2.ClientID = @nClientID)
      GROUP BY
        BP.ClientProductID

    ) AS Y ON (Y.ClientProductID = CP.ClientProductID)
    LEFT JOIN (

      SELECT
        PR.ProductID,
        COUNT(*) AS AttendanceCount
      FROM
        taProducts AS PR
          LEFT JOIN tbAppointmentTypes AS AT ON (AT.ProductCategory = PR.ProductCategory)
          LEFT JOIN taAttendanceExclusion AS AE ON ((AE.ProductID = PR.ProductID) AND (AE.AttendanceTypeID = AT.AttendanceTypeID))
      WHERE
        (AT.IsActive = 1) AND
        (AT.IsService = @nIsService) AND
        (AE.AttendanceTypeID IS NULL) AND
        (TRIM(AT.AppointmentType) <> '')
      GROUP BY
        PR.ProductID

    ) AS Z ON (Z.ProductID = CP.ProductID)
WHERE
  (CP.ClientID = @nClientID) AND
  (CP.Status = 1) AND
  (PC.CoverAllowed = 1) AND
  (X.AttendanceCount > 0) AND
  (Z.AttendanceCount > 0)
ORDER BY
  IF(PR1.ProductName IS NULL, PR.ProductName, CONCAT(PR1.ProductName, '|', PR.ProductName))
