MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:David Legault Date:June 15 2005 12:58pm
Subject:Re: Tough queries
View as plain text  
Thanks for the reply, but I think you misinterpreted what I'm looking 
for here.

For the first query I want to be able to get a row record of (in one 
query possibly)

team_name for first team (team_id1)
team_name for second team (team_id2)
and each of these team points

for a list of N games retreived (and NULL values for the scores if there 
are no points).

Same kind of query for the player stats where I'd retreive these infos 
on each row record :

total goals
total assists
total points
sorted by total points DESC



mfatene@stripped wrote:
> Hi,
> for the first query,
> select concat(team_id,' (',sum(points),')') from games,points
> where games.game_id=points.game_id
> and games.team_id1=points.team_id
> group by team_id
> can solve the problem.
> For the second, join players and points.
> Mathias
> Selon David Legault <dlegault@stripped>:
>>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.
>>MySQL General Mailing List
>>For list archives:
>>To unsubscribe:
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