Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
02-06-2005, 02:34 PM #1
- Join Date
- Apr 2004
- Thanked 0 Times in 0 Posts
Join Two Tables in this Complicated Query
Hi to All
I've two tables
First Table Player
tbl_player (fld_id, fld_dor, fld_tname, fld_tpass, fld_name, fld_email, fld_mobile)
Second Table Attempt
tbl_attempt (fld_attempt_id, fld_dop, fld_tname, fld_pid, fld_pscore)
Relation of First Table with Second Table is fld_tname and fld_pid of tbl_attempt with fld_id of tbl_player
Basically this is a 2-player game in which tbl_player contains both player a and player b details (they are identified by same Team Name). Table Attempt (tbl_attempt) holds individual scores which again can be grouped as a team score by the field fld_tname.
What i wish to do is display the total team score
with individual scores for eg
Team Name________________________Total Score
Player A(500), Player B(200)__________________700
Player C(500), Player D(900)__________________1400
I've tried this query
set recordset=connection.execute("select top 10 a.fld_tname as teamname, max(a.fld_pscore) as maxscore, sum(a.fld_pscore) as totalscore, b.fld_name as name from tbl_attempt a, tbl_player b where datediff(dd,a.fld_dop,getdate())=0 and a.fld_tname = b.fld_tname and a.fld_pid = b.fld_id group by a.fld_tname order by totalscore desc")
How do i create this query in ASP. Please help me out if you can, i will appreciate it.
Thanks a lot
Last edited by maskme; 02-06-2005 at 04:16 PM.