Wrox Programmer Forums
|
ASP Pro Code Clinic As of Oct 5, 2005, this forum is now locked. No posts have been deleted. Please use "Classic ASP Professional" at: http://p2p.wrox.com/forum.asp?FORUM_ID=56 for discussions similar to the old ASP Pro Code Clinic or one of the other many remaining ASP and ASP.NET forums here.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP Pro Code Clinic section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 6th, 2005, 09:43 AM
Registered User
 
Join Date: Apr 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
XYZ
Player A(500), Player B(200)__________________700

PQR
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
Yusuf
 
Old February 7th, 2005, 12:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Query use in asp file.

<%
set cn=server.CreateObject("adodb.connection") // open connection
cn.ConnectionString="....." // connection string
cn.Open


ssql="SELECT * from tablename" // Query

set rs=cn.Execute(ssql)

do while rs.eof=false

Response.Write("<td>" & rs("field1") & "</td>")
Response.Write("<td>" & rs("field2") & "</td>")
.....
..

rs.movenext
loop
%>


Mateen



Quote:
quote:Originally posted by maskme
 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
XYZ
Player A(500), Player B(200)__________________700

PQR
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
Yusuf







Similar Threads
Thread Thread Starter Forum Replies Last Post
Inner join query with multiple tables.. rupen Access 2 April 9th, 2007 11:16 AM
Seemingly Complicated Query help needed krashed SQL Language 5 March 31st, 2006 01:02 AM
How do you query this complicated thing? sprion SQL Language 5 January 6th, 2005 02:06 AM
complicated query rajanikrishna Classic ASP Basics 4 June 17th, 2004 04:17 PM
Complicated Query roniestein Access 3 December 13th, 2003 10:34 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.