www.webdeveloper.com
Results 1 to 3 of 3

Thread: sql Inner Join

  1. #1
    Join Date
    Oct 2004
    Posts
    34

    sql 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
    Dec 2004
    Location
    Eau Claire, WI
    Posts
    153
    Maybe this will work.. This assumes that there is only one record in the showrank table to match the one record in the register table. It would be different if I was coding a ONE->MANY relationship.

    SELECT register.Player_Name, showrank.totalGainLoss
    FROM register INNER JOIN showrank ON register.id = showrank.id
    ORDER BY showrank.totalGainLoss DESC;

    -Brad

  3. #3
    Join Date
    Feb 2003
    Posts
    2,745
    Code:
    SELECT	Top 1 PlayerName, Max(Totalgainloss) as highGainLoss
    FROM	register r
    INNER JOIN showrank s
    On    s.id = r.id
    GROUP BY PlayerName
    ORDER by Max(Totalgainloss) DESC

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles