MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:mark warren bracher Date:June 21 2005 8:50pm
Subject:Re: Tough queries
View as plain text  
David Legault wrote:

> 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).


select g.game_id,
       concat( t1.name, ' (', count(p1.point_id), ') - ',
               t2.name, ' (', count(p2.point_id), ')' )
 from games g, teams t1, teams t2, points p1, points p2
 where g.team_id1 = t1.team_id
  and g.team_id2 = t2.team_id
  and g.team_id1 = p1.team_id
  and g.team_id2 = p2.team_id
 group by g.game_id, t1.name, t2.name

you need to include game_id to handle the case where two teams play more
than once, don't want to roll the scoring for the two teams together.

I'm assuming that for game score you're looking for just points scored,
not assists and other stuff.

> 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


select p.player_name,
       count( p1.point_id ) as goals,
       count( p2.point_id ) + count( p3.point_id ) as assists,
       count( p1.point_id ) + count( p2.point_id ) + count( p3.point_id
) as points
 from  players p, points p1, points p2, points p3
 where p.player_id = p1.goal_player_id
  and  p.player_id = p2.pass_player_id1
  and  p.player_id = p3.pass_player_id2
 group by p.player_name
 order by 4 desc
 
 
do we need to handle the double-counting case in which the scoring
player also has the first pass?  that starts to get tricky; at that
point I'd start pulling the data and handling it programmatically.

heck, I'd probably handle this programmatically as well, but handling it
in sql made for a nice diversion...  ;-)

how about players with the same name?  john smith or such...  at that
point I suppose you could include player_id in the select, just like I
included game_id above...

note, I haven't actually tested the above.  I'm pretty lazy and didn't
want to make up test data.  I've probably got a typo somewhere, but i
think it's probably pretty close.

- mark

> 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