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 :

player_name
total goals
total assists
total points
sorted by total points DESC

Thanks

David

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>:
> 
> 
>>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
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>>
> 
> 
> 
> 
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