Query help with scores

[eluser]Kevv[/eluser]
Something like this should do what you want;

Code:

SELECT s.member_id, COUNT(s.member_id) AS 'birdies' FROM scores s
JOIN holes h ON s.hole_id=h.hole_id AND s.gross_score+1=h.hole_par
WHERE h.hole_id=2 /* limit to specific hole */
GROUP BY s.member_id
ORDER BY COUNT(s.member_id) DESC

You'll probably want to add another join to get the member name too.