1. Junior Member
Join Date
Dec 1969
Posts
21

Hi all,<BR>Just looking for a piece of advice on how to go about doing some on-the-fly math between two tables and I&#039;m not sure which is the best way to go...<BR><BR>I&#039;d like to have a sum of the values (from table 2) based on what a user has chosen (stored in table 1). <BR><BR>Table 1: UserID, Name, F1, F2, F3, F4<BR>Table 2: PlayerID, Name, PlayerClass, Points<BR><BR>The F entries in Table 1 correspond to the PlayerClass in Table 2 but contain the PlayerID of the record in Table 2. Meaning that there are 4 players in Table 2 that are considered F1, 4 players that are considered F2, etc... but the user can only pick one from each F class.<BR><BR>Confused? Me too. I can provide more info if you need it. Any suggestions would be greatly appreciated! I&#039;m thinking a double for-loop may be my only option, but am worried it&#039;ll take forever to chug through... The only other thing I can think of is to create an array of the chosen PlayerIDs from Table 1 and then doing some kind of sum operation using that array with Table 2&#039;s points...

2. Senior Member
Join Date
Dec 1969
Posts
96,118

You should probabaly have a THIRD table that relates UserID to PlayerID.<BR><BR>Maybe something like:<BR> LinkTable:<BR> userID : FK to Table1<BR> playerID : FK to Table2<BR> <BR>And then it&#039;s easy:<BR><BR>SELECT U.Name, Sum(P.Points) AS TotalPoints<BR>FROM Table1 AS U, Table2 AS P, LinkTable AS L<BR>WHERE L.UserID = U.UserID<BR>AND P.PlayerID = L.PlayerID<BR>GROUP BY U.Name<BR>ORDER BY U.Name<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## But if you want to stick with this...

...it&#039;s not too hard:<BR><BR>SELECT U.Name, P1.Points+P2.Points+P3.Points+P4.Points AS TotalPoints<BR>FROM Table1 AS U, Table2 AS P1, Table2 AS P2, Table2 AS P3, Table2 AS P4<BR>WHERE P1.PlayerID = U.F1<BR>AND P2.PlayerID = U.F2<BR>AND P3.PlayerID = U.F3<BR>AND P4.PlayerID = U.F4<BR>ORDER BY U.Name<BR><BR><BR>

4. Junior Member
Join Date
Dec 1969
Posts
21

## Thanks Bill, worked like a charm! <eop>

&#060;eop&#062;

#### Posting Permissions

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