SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2002
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Retrieving data from multiple tables

    This is an interesting one..for me anyway...I am writing a ranking system for a game (Descent 3). I have two tables, and I need to pull a few columns from one, and 2 count()'s from the other.

    Table 'Pilot'
    Pilot_ID
    Pilot_Name

    Table 'Match'
    Count(*) Where Pilot_ID=Pilot.Pilot_ID
    Count(*) Where Pilot_ID=Pilot.Pilot_ID and Match_Win=1

    How can I sub-query the 'Match' table to get those 2 Count()'s so I don't have to do this?:

    Code:
    Set RS=OpenRS("SELECT Pilot_ID,Pilot_Name FROM Pilot")
    Do While NOT RS.EOF
       Set RS1="SELECT Count(*) FROM Match WHERE Pilot_ID=RS(PilotID)
       Set RS2="SELECT Count(*) FROM Match WHERE Pilot_ID=RS(PilotID) AND Match_Win=1
       .
       .
    Loop
    ugly, eh? I could pop 2 columns in my pilot table, to hold total matches, and total won matches, if the query is too much of a pain...would this be considered bad form though?

  2. #2
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Mar 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this:

    Code:
    SELECT   P1.Pilot_ID,
             P1.Pilot_Name,
             COUNT(M1.Pilot_ID) AS Num_Matches,
             COUNT(M2.Pilot_ID) AS Num_Wins
    FROM     Pilot P1,
             Match M1,
             Match M2
    WHERE    P1.Pilot_ID = M1.Pilot_ID
    AND      P1.Pilot_ID = M2.Pilot_ID
    AND      M2.Match_Win = 1
    GROUP BY P1.Pilot_ID,
             P1.Pilot_Name

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sorry, darkeye, that won't work

    it's a cross-join and will grossly inflate the totals -- i tested it

    try this instead --
    Code:
    select p1.pilot_id, pilot_name
         , count(*) as matches
         , sum(match_win) as wins
      from pilot p1, metch m1
     where p1.pilot_id = m1.pilot_id
    group by p1.pilot_id, pilot_name
    note that it depends on match_win=1 being "summable" (i made that word up)

    if match_win were something else, you could use a case structure to achieve the same thing --
    Code:
    select p1.pilot_id, pilot_name
         , count(*) as matches
         , sum(
            case when match_win='y'
              then 1 else 0
            end ) as wins
      from pilot p1, metch m1
     where p1.pilot_id = m1.pilot_id
    group by p1.pilot_id, pilot_name
    p.s. i used "metch" as the table name because "match" is a reserved word
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Mar 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    your right. i wasnt thinking. but wont my code work if you do this:

    Code:
    SELECT   P1.Pilot_ID,
             P1.Pilot_Name,
             COUNT(DISTINCT M1.Pilot_ID) AS Num_Matches,
             COUNT(DISTINCT M2.Pilot_ID) AS Num_Wins
    FROM     Pilot P1,
             Metch M1,
             Metch M2
    WHERE    P1.Pilot_ID = M1.Pilot_ID
    AND      P1.Pilot_ID = M2.Pilot_ID
    AND      M2.Match_Win = 1
    GROUP BY P1.Pilot_ID,
             P1.Pilot_Name
    ???

    i cant test this but ive run into the same problem with a cross-join before. it returns the correct value squared, right? if so making the count DISTINCT should fix it.
    Last edited by -TheDarkEye-; Sep 2, 2002 at 00:55.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sorry, that won't work either

    count(distinct pilot_id) will return how many different pilot_ids for each pilot?

    one

    change it to count the distinct match_ids per pilot, and it works

    still, i don't see the benefit of deriving sums from N-squared rows, when you can get them easily and painlessly from N

    rudy
    Last edited by r937; Sep 2, 2002 at 05:52.

  6. #6
    SitePoint Enthusiast
    Join Date
    Jan 2002
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I gave you guys one screwed up example, I apologize

    BUT! You're suggestions got me on the ball, and I'm getting there! And yes, Matches is a reserved word, I found out...the hard way

    Here's the actual table structures: (uneeded fields omitted)

    Table LUSER
    Luser_ID
    Luser_LuserName
    Pilot_Rank
    Pilot_WinStreak

    Table BATTLE
    Battle_ID
    Battle_Pilot1
    Battle_Pilot2
    Battle_Winner


    Here's the Query as it stand now:
    SELECT
    Luser.luser_ID,Luser.Luser_LuserName,Luser.Pilot_Rank,Luser.Pilot_WinStreak,COUNT(*) AS TotalMatches,SUM(Battle.Battle_Winner=Luser.Luser_ID) AS TotalWins
    FROM
    Luser,Battle WHERE Luser.Luser_ID=Battle.Battle_Pilot1 OR Luser.Luser_ID=Battle.Battle_Pilot2
    GROUP BY
    Luser.Pilot_Rank,Luser.Luser_ID,Luser.Luser_LuserName,Luser.Pilot_WinStreak

    Which returns: (Luser.Pilot_WinStreak is not implimented yet)
    Code:
    Pilot_Rank Luser_ID Luser_LuserName Pilot_WinStreak TotalMatches TotalWins 
    1  6 Puffer     0 2 -2 
    2  5 Scratch    0 7 -4 
    3  3 Sting Ray  0 6 -3 
    5  4 Vlider     0 8 -1 
    10 2 Blitzkrieg 0 9 -6
    Which, except for the negative numbers (which are correct, just negative) is what I need, but in addition, I need ALL the pilot in the competition, not just the ones who've played a match. Eventually, yes, everyone will have played, but when the competition first starts, ranking will be spaced out, like shown.

    To try to get all players listed, I tried this query:
    SELECT
    Luser.luser_ID,Luser.Luser_LuserName,Luser.Pilot_Rank,Luser.Pilot_WinStreak,COUNT(*) AS TotalMatches,SUM(Battle.Battle_Winner=Luser.Luser_ID) AS TotalWins
    FROM
    Luser,Battle WHERE Luser.Luser_ID=Battle.Battle_Pilot1 OR Luser.Luser_ID=Battle.Battle_Pilot2
    GROUP BY
    Luser.Pilot_Rank,Luser.Luser_ID,Luser.Luser_LuserName,Luser.Pilot_WinStreak

    Which returns:
    Code:
    Pilot_Rank luser_ID Luser_LuserName Pilot_WinStreak TotalMatches TotalWins 
    1  6  Puffer     0 16 -2 
    2  5  Scratch    0 16 -4 
    3  3  Sting Ray  0 16 -3 
    4  7  Tweety     0 16 0 
    5  4  Vlider     0 16 -1 
    6  8  JBomB      0 16 0 
    7  9  Ferno      0 16 0 
    8  10 Roncli     0 16 0 
    9  11 Raider     0 16 0 
    10 2  Blitzkrieg 0 16 -6 
    11 12 NoFX       0 16 0 
    12 13 Psion      0 16 0 
    13 14 Slim       0 16 0 
    14 15 Whip       0 16 0
    oops, the TotalMatches field is..how do you say..WRONG

    So I'm trying to replace the "Count(*) AS TotalMatches" with some SUM()'s, and well, lookit!

    SELECT Luser.Pilot_Rank,Luser.Luser_ID,Luser.Luser_LuserName,Luser.Pilot_WinStreak,SUM(Battle.Battle_Pilot1=Luser.Luser_ID)As TotalMatches1,SUM(Battle.Battle_Pilot2=Luser.Luser_ID) AS TotalMatches2,SUM(Battle.Battle_Winner=Luser.Luser_ID) AS TotalWins FROM Luser,Battle GROUP BY Luser.Pilot_Rank,Luser.Luser_ID,Luser.Luser_LuserName,Luser.Pilot_WinStreak

    Returns
    Code:
    Pilot_Rank Luser_ID Luser_LuserName Pilot_WinStreak TotalMatches1 TotalMatches2 TotalWins 
    1  6  Puffer     0 -2  0 -2 
    2  5  Scratch    0 -2 -5 -4 
    3  3  Sting Ray  0 -3 -3 -3 
    4  7  Tweety     0  0  0  0 
    5  4  Vlider     0 -3 -5 -1 
    6  8  JBomB      0  0  0  0 
    7  9  Ferno      0  0  0  0 
    8  10 Roncli     0  0  0  0 
    9  11 Raider     0  0  0  0 
    10 2  Blitzkrieg 0 -6 -3 -6 
    11 12 NoFX       0  0  0  0 
    12 13 Psion      0  0  0  0 
    13 14 Slim       0  0  0  0
    14 15 Whip       0  0  0  0
    Yay! So, anyone know how I can add TotalMatches1 and TotalMatches2 within the query?
    Last edited by Metallisoft; Sep 2, 2002 at 11:41.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    to get all the pilots, regardless of whether they have any battles, you need a LEFT JOIN
    Code:
    FROM Luser LEFT JOIN Battle 
     ON Luser.Luser_ID = Battle.Battle_Pilot1 
     OR Luser.Luser_ID = Battle.Battle_Pilot2
    WHERE ...
    for calculating the total number of matches, does it matter whether it was pilot1 or pilot2? do you need to keep them as separate totals? i'm just asking, because in other sports, it does matter if you are Home team or Away team...

    rudy

  8. #8
    SitePoint Enthusiast
    Join Date
    Jan 2002
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Naah, pilot 1 is the person who submitted the match.

    I tried SUM(SUM()+SUM()), and SQLServer barfed. Oh, but I didn't try Sum()+Sum(), wonder if that would work.
    Last edited by Metallisoft; Sep 3, 2002 at 04:50.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    well, if separate totals does not matter, try the left outer join, joining battle just once, not twice, and use count(*) for battles and sum(case) for wins

    rudy
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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