List:General Discussion« Previous MessageNext Message »
From:David Legault Date:June 14 2005 7:59pm
Subject:Tough queries
View as plain text  
Hello,

I'm a regular user of MySQL but lately on a personal project I've run 
into some very complexe query management and am a little bit confused on 
how to get it working. This is a hockey league stats website application.

I have 4 tables with the following columns:

teams -> team_id | team_name
players -> player_id | team_id | player_name
games -> game_id | team_id1 | team_id2
points-> point_id | game_id | team_id | goal_player_id | pass_player_id1 
| pass_player_id2

The kind of queries I'd like to perform would be (if possible in one 
query or a subset of queries) something to generate the following as a 
list of N games with the scores (if the game was played and team name) 
which would use the games, teams and points tables.

Desired Output

Team A (2) - Team B (7)
Team D (3) - Team C (1)
...

Thus, it needs to retreive the team names, and total score for each game 
that I want to list (using other criteria not essential in the example)

Another Query would be to have the points of each player listed for a 
team (in ORDER DESC or total points):

Team C:

Player | Goals | Assists | Points

AA 8 1 9
BB 5 3 8
CC 3 2 5
DD 1 2 3
....

If you could explain a little bit how each query answer you provide 
works, I'd like it. If you have any tutorials or good articles about 
such complexe queries, I'd be thankfull to be able to read them to help me.

Thanks

David
Thread
Tough queriesDavid Legault14 Jun
  • Re: Tough queriesmfatene14 Jun
    • Re: Tough queriesDavid Legault15 Jun
      • Re: Tough queriesmark warren bracher21 Jun
  • Visual Basic .NET Oledb ProviderLeonardo Javier BelĂ©n15 Jun
  • Re: Visual Basic .NET Oledb ProviderFredrick Bartlett15 Jun