jeudi 10 mars 2016

Unsure how to subquery with multiple rows returned

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