inner join

gop373 · #1 (**permalink**) 12-08-04, 10:28

I have two tables. The first table name register.
id
Player_name
Email

Second table name showrank
id
Totalgainloss
CashAV

I want to show the Player_name who get the maximum Totalgainloss and also sho the number 0f totalgainloss.
I tried

sql="SELECT max(totalgainloss)as cashAV showrank.*, register.* FROM showrank INNER JOIN register ON showrank.id = register.id;"
set HighPrice=server.createobject("adodb.recordset")
HighPrice.open sql,conn,1,3

if not (HighPrice.eof) then
response.write HighPrice("cashAV")
response.write HighPrice("Player_name")
end if

But it did not work !!

Littlefoot · #2 (**permalink**) 12-08-04, 11:48

Would this be OK?

Code:

SELECT r.player_name, s.totalgainloss
FROM register r, showrank s
WHERE r.id = s.id
  AND s.totalgainloss = (SELECT MAX(s1.totalgainloss)
						 FROM showrank s1
						);

Internet Services

Web Development

Digital Marketing

Consumer Tech