So I have this data in SQLite:
Group:
id name
1 GroupA
2 GroupB
3 GroupC
Participant:
id name group location points
1 ParA 1 1 10
2 ParB 2 1 5
Location:
id name
1 LocA
2 LocB
And I can't quite work out how to structure a query.
Each participant is a member of a group, has a location and a certain number of points. Think of it like a competition, where the aim is for a group to get the most points at all locations.
I need to work out at what locations GroupA have beaten GroupB in points - and I'm curious if it can be done in a single query (with sub queries) rather than in PHP like my current plan.
ParA for example is in group 1 and at location 1, ParB in group 2 and also location 1. ParA has more points, so GroupA would be returned by the query - as it has more points than GroupB at location 1. There can be a number of groups and locations, each with varying numbers of members - so multiple rows would be generated where GroupA has the most points (which is why I think this may be possible in SQL).
Hopefully this question is appropriate. I can't find any questions on here similar, so I figured I'd ask it myself!
Aucun commentaire:
Enregistrer un commentaire