Generic syntax for statistics

[iannorthwood]

I have a bunch of tables for my basketball club's d/b. I want to be able to interrogate them to build a web page showing player stats. For example, I want a table showing the top 5 scorers, top 5 3-point scorers, etc. What's got me foxed is how to join it all up and then sort it into the top 5 order. The main tables in question are T_Member, T_Fixture, T_Season and T_FixtureTeam:

T_Member:
MemberNo, FirstName, LastName, (etc)

T_Fixture:
FixtureNo, SeasonNo, MatchDate, (etc)

T_Season:
SeasoNo, SeasonDescription, SeasonStartDate, SeasonEndDate, CurrentSeason

T_FixtureTeam:
FixtureNo, MemberNo, 2Points, 3Points,Fouls,FreeShotAttempts,FreeShotsHit,Assists

Where you see the same field name, that field data is common for all tables that it appears in. In other words, for example, the T_FixtureTeam 'FixtureNo' and 'MemberNo' fields are made up of 'T_Fixture.FixtureNo' and 'T_Member.MemberNo' respectively.

Ready?
The user will select a season at the top of the page (although there's a default to the current season (CurrentSeason is a Boolean field). The query needs to:
- Find all fixtures in that season
- Find all members who played in those fixtures
- Find the top 5 3-point scorers (or whatever stat I'm gathering) for those matches
- Sort the resulting data

I figure I need a JOIN on MemberNo but, as I say, the rest of it has my head spinning.

TIA

 

[LKBrwnDBA]

Is this some kind of homework assignment?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[iannorthwood]

No. For as long as I can remember I've been promising the members of the club that our page will have a 'Statistics' page. I already did the easy stuff (most appearances, tallest, heaviest) but now I need help with the more complex queries.