SELECT `s`.`subid`, `s`.`base` AS `basetype`, `s`.`code`, `s`.`name`, `s`.`state`, `s`.`period_restriction_type`, `s2`.`base_name`, `s`.`base` AS `sessions`,
       GROUP_CONCAT(DISTINCT cl.classifier_id) AS `classifiers`, `s`.`type`,
       CASE WHEN (s.period_restriction_type = 2 AND s.begin IS NULL) THEN s.begin_planned ELSE s.begin END AS `begin`,
       CASE WHEN (s.period_restriction_type = 2 AND s.end IS NULL) THEN s.end_planned ELSE s.end END AS `end`,
       `s`.`period`, `s`.`longtime`,`s`.`plan_users`, COUNT(DISTINCT st.mid) AS `students`

FROM `subjects` AS `s`
LEFT JOIN `Students` AS `st` ON st.CID = s.subid
LEFT JOIN `classifiers_links` AS `cl` ON s.subid = cl.item_id AND cl.type = 0
LEFT JOIN (SELECT `subjects`.`subid` AS `base_id`, `subjects`.`name` AS `base_name` FROM `subjects`) AS `s2` ON s.base_id = s2.base_id
INNER JOIN `Teachers` AS `teachers` ON s.subid = teachers.CID
WHERE (teachers.MID = '140')

GROUP BY `s`.`subid`,
         `s`.`base`,
         `s`.`name`,
         `s`.`code`,
         `s`.`state`,
         `s`.`period_restriction_type`,
         `s`.`begin`,
         `s`.`begin_planned`,
         `s`.`end_planned`,
         `s`.`end`,
         `s`.`period`,
         `s`.`price`,
         `s`.`external_id`,
         `s`.`type`,
         `s`.`longtime`,
         `s`.`base_id`,
         `s`.`base`
ORDER BY `name` ASC
LIMIT 25;