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
[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]
