View Full Version : Join Two Tables in this Complicated Query

02-06-2005, 02:34 PM
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")

but it times out, i dont know whats wrong with it

How do i create this query in ASP. Please help me out if you can, i will appreciate it.

Thanks a lot