sql query with sum and max in two tables

[babsi]

table a
id_A''''''''''' ''''''''name
1'''''''''''''' ''''''''''''''t om
2'''''''''''''' ''''''''''''''f rank
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.i d_a,table_a.Nam e,
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,ta ble_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,ta ble_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,ta ble_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.