SELECT r.ID, r.FirstName, r.LastName, m.TotalMPs
FROM roster r
INNER JOIN (
SELECT ID, MAX(TotalMPs) as TotalMPs
FROM MPs
GROUP BY ID
) m
ON r.ID = m.ID AND m.TotalMPs <= "10"
WHERE r.eMail IS NOT NULL
AND r.Subscribe = "Y"
AND r.Status = "A"
ORDER BY r.LastName, r.FirstName
CLICK HERE to find out more related problems solutions.