SELECT DISTINCT parent.`EMAIL` AS ADDRESS, 3 AS MESSAGE_TEMPLATE_ID, 0 AS STATUS, parent.ID AS MEMBER_ID, count(schedules.ID) as schedules_count FROM members as student LEFT JOIN members as `parent` on `parent`.`ID` = student.PARENT_ID LEFT JOIN members_classes on student.ID = members_classes.MEMBER_ID LEFT JOIN schedules on schedules.CLASS_ID = members_classes.CLASS_ID AND schedules.START > '2016-10-11 12:00:00' WHERE parent.LOGIN_GROUP IN ('client') AND parent.STATUS = 1 GROUP BY schedules.ID

There is such a query, how can I make only those lines output where count (schedules.ID) is greater than 0?

  • add to WHERE AND schedules_count> 0 tried? - Broouzer King
  • one
    Doesn’t fit, because WHERE works before the table joins - Jonny Manowar

1 answer 1

Use HAVING to:

 SELECT DISTINCT parent.`EMAIL` AS ADDRESS, 3 AS MESSAGE_TEMPLATE_ID, 0 AS STATUS, parent.ID AS MEMBER_ID, count(schedules.ID) as schedules_count FROM members as student LEFT JOIN members as `parent` on `parent`.`ID` = student.PARENT_ID LEFT JOIN members_classes on student.ID = members_classes.MEMBER_ID LEFT JOIN schedules on schedules.CLASS_ID = members_classes.CLASS_ID AND schedules.START > '2016-10-11 12:00:00' WHERE parent.LOGIN_GROUP IN ('client') AND parent.STATUS = 1 GROUP BY schedules.ID HAVING schedules_count > 0;