MySQL subquery select max value in secondary table with conditionals in both tables

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.

Leave a Comment

Your email address will not be published.

Scroll to Top