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
>
> [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]>:
>>
>>
>>> 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/[EMAIL PROTECTED]
>>>
>>>
>>
>>
>>
>>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
