SELECT
sc.id, u.telefono, sc.servicio_id,
FROM mobi_suscripcion AS sc
INNER JOIN mobi_usuariomovil AS u ON u.id = sc.usuario_id AND u."idEstado" = TRUE
INNER JOIN mobi_servicio AS s ON sc.servicio_id = s.id

LEFT JOIN mobi_mensajemt AS mt ON mt.estado = 100 AND u.id = mt.usuario_id
LEFT JOIN mobi_contenidomensaje AS co ON co.id = mt.contenido_id AND co.servicio_id = 23
LEFT JOIN mobi_envio AS env ON env.id = mt.envio_id AND env.fecha_envio BETWEEN  '2011-12-19' AND '2011-12-19'

WHERE  sc.servicio_id = 23
AND sc.estado = TRUE
AND sc.fecha_suscripcion < '2011-12-19'

AND env.fecha_envio BETWEEN  '2011-12-19' AND '2011-12-19'

GROUP BY sc.id, u.id, s.id
HAVING COUNT(mt.id) < s.max_mt_day;