MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:mfatene Date:June 14 2005 9:22pm
Subject:Re: Tough queries
View as plain text  
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