Results 1 to 2 of 2

Thread: inner join

  1. #1
    Join Date
    Aug 2004
    Posts
    77

    Unanswered: inner join

    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 !!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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
    						);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •