Retrieving data from multiple tables

[Metallisoft]

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?:

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?

[-TheDarkEye-]

try this:

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

[r937]

sorry, darkeye, that won't work

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

try this instead --

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

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

[-TheDarkEye-]

your right. i wasnt thinking. but wont my code work if you do this:

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.

[r937]

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

[Metallisoft]

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)

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:

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

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?

[r937]

to get all the pilots, regardless of whether they have any battles, you need a LEFT JOIN

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

[Metallisoft]

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.

[r937]

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