I have a mySQL database with four tables and a site that calls data from them and displays it in dynamic html tables.
The database records players scores at various venues, and I need to develop a query that will get only the players who have cumulative points of over 750 in any one venue, if a player has more than 750 points in two or more venues then it only shows the highest of these points totals and the venue name they were scored at.
So if Bob has 5 games in venue A totaling 650 points and 7 games in venue B totalling 800 points only venue Bs points total and venue name are shown..hope that makes sense.
The below code show a query I have at the moment that totals all players for a single venue, this works fine, but I can't ammend it to do what I now need.
I Think I need to add something like AND SUM(Position.Points) >= '750' but that wont work and even if it did it would return all points over 750 not the highest over 750 for each player.
I hope that makes sense, I'm new to sql and databases in general so if you can help I'd really appreciate it.
SELECT SUM(Position.Points) , Player.FirstName, Player.LastName, COUNT(Results.MembershipNo)
FROM Position, Player, Results, Venue
WHERE Player.MembershipNo = Results.MembershipNo
AND Results.Position = Position.Position
AND Venue.VenueID = Results.VenueID
GROUP BY Player.MembershipNo
ORDER BY SUM(Position.Points) DESC