Help for left join and sum

[jansky]

To all guru

first of all please excuse my bad english

I am creating a basketball statistics and one of required is to list all teams with total number of win and total number of lost
my problem is how do i list all the teams even the team did not played yet

here is the original query

select sum(game_result.win) as win,sum(game_result.lost) as lost, game_result.team_id ,
team_profile.team_name as teamname
from game_result
left join team_profile on (game_result.team_id=team_profile.team_id)
group by game_result.team_id order by win desc limit 5

• This list top 5 teams who played, unfortunately if i have 10 teams and only 3 teams played it will list only 3 teams. How would i list all 5 teams

and to find solution i revise my code to

SELECT team_profile.team_name AS teamname,
FROM team_profile
LEFT JOIN (

SELECT game_result.team_id, sum( game_result.win ) AS win, sum( game_result.lost ) AS lost from game_result group by team_id ORDER BY win DESC
) AS oh ON team_profile.team_id = oh.team_id

LIMIT 5

but only i get sql error

"1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM team_profile
LEFT JOIN ("

thanks for your help in advance

[jansky]

To all guru

first of all please excuse my bad english

I am creating a basketball statistics and one of required is to list all teams with total number of win and total number of lost
my problem is how do i list all the teams even the team did not played yet

here is the original query

select sum(game_result.win) as win,sum(game_result.lost) as lost, game_result.team_id ,
team_profile.team_name as teamname
from game_result
left join team_profile on (game_result.team_id=team_profile.team_id)
group by game_result.team_id order by win desc limit 5

• This list top 5 teams who played, unfortunately if i have 10 teams and only 3 teams played it will list only 3 teams. How would i list all 5 teams

and to find solution i revise my code to

SELECT team_profile.team_name AS teamname
FROM team_profile
LEFT JOIN (

SELECT game_result.team_id, sum( game_result.win ) AS win, sum( game_result.lost ) AS lost from game_result group by team_id ORDER BY win DESC
) AS oh ON team_profile.team_id = oh.team_id

LIMIT 5

but only i get sql error

"1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM team_profile
LEFT JOIN ("

[NogDog]

Maybe...

SELECT tp.team_name AS teamname, SUM(gr.win) AS win, SUM(gr.lost) as lost
FROM team_profile AS tp
LEFT JOIN game_result AS gr ON tp.team_id = gr.team_id
GROUP BY tp.team_name
ORDER BY win DESC, lost ASC

[NogDog]

Please do not cross-post in multiple forums. I am merging this thread with the other one....