sql query with sum and max in two tables

[babsi]

table a
id_A'''''''''''''''''''name
1''''''''''''''''''''''''''''tom
2''''''''''''''''''''''''''''frank
3 ''''''''''''''''''''''''john

table b:
id_A'''''''''''''''''''''''''''''''''''id_C'''''''''''points
1'''''''''''''''''''''''''''''''''''''''''''''''''1'''''''''''''''''''''1
1'''''''''''''''''''''''''''''''''''''''''''''''''2'''''''''''''''''''''5
1'''''''''''''''''''''''''''''''''''''''''''''''''3'''''''''''''''''''''7
2'''''''''''''''''''''''''''''''''''''''''''''''''1''''''''''''''''''' 5
2''''''''''''''''''''''''''''''''''''''''''''''' 2'''''''''''''''''''''12

table C:
id_C, sport

i have to write query that have everything from table A with sum(points) for every person from B along with id_C and points for sport with highest score.table should lok like this:

id_A, name, sum(pts), max(pts), id_C for max(pts)

example for tom:
id_A:1
name:tom
sum(pts): 13
max(pts):7
idC for max(pts):3

[deepuv04]

this is the query for my understand
Try,
SELECTtable_a.id_a,table_a.Name,
SUM(Points),MAX(Points),Max(Id_C)
from Table_a inner join
table_b on table_a.id_a = table_b.id_a
group by table_a.id_a,table_a.Name

I have a question why did you mention the table C

Thanks,
Sandeep

[babsi]

I have a question why did you mention the table C

because table b's primary key is consist of id_a and ib_c

table c:
id_c'''''''''''''''''''game
1'''''''''''''''''''''''''''poker
2''''''''''''''''''''''''''free cell
3''''''''''''''''''''''''''solitaire


tom played 3 games and he won 13 points (sum of pts in b). highest score he achieved in game number 3 solitaire (he won 7 pts in solitaire). result table looks like this

id_A'''name''''''sum(p)''''''max(p)'''''''''game
1'''''''''''''tom''''''''''''''13'''''''''''''''''''7'''''''''''''''''''''''solitaire
2''''''''''''frank''''''''''''17'''''''''''''''''''12'''''''''''''''''''free cell
3''''''''''''john'''''''''''''0''''''''''''''''''''''0'''''''''''''''''''''''0

name of games are not important. it could only be id of games.

[deepuv04]

ok use the following for getting the id of the game
similarly you can ge the game name also
SELECT table_a.id_a,table_a.Name,
SUM(Points),MAX(Points),
(SELECT TOP 1 Id_C FROM table_B WHERE ID_A = table_A.ID_A ORDER BY Points desc)
from table_a inner join
table_b on table_a.id_a = table_b.id_a
group by table_a.id_a,table_a.Name

[babsi]

thanks deepuv04, it works. I just changed inner join to left join to include people without pts. but thanks a lot once more.