prizes
+------------+------------+---------+---------+
| l_playerid | s_playerid | l_prize | s_prize |
+------------+------------+---------+---------+
| 1 | 1 | 500 | 350 |
| 2 | 1 | 650 | 245 |
| 1 | 2 | 500 | 65 |
| 2 | 2 | 150 | 85 |
| 3 | 3 | 95 | 125 |
| 5 | 2 | 350 | 205 |
+------------+------------+---------+---------+
players
+----------+----------+
| playerid | name |
+----------+----------+
| 1 | Bob |
| 2 | Jay |
| 3 | Suzie |
| 4 | David |
| 5 | Rick |
| 6 | Jane |
+----------+----------+
I need:
+-------+----------------+----------------+
| name | total_l_prizes | total_s_prizes |
+-------+----------------+----------------+
| Bob | 1000 | 595 |
| Jay | 800 | 355 |
| Suzie | 95 | 125 |
| Rick | 350 | 0 |
+-------+----------------+----------------+
attempt:
SELECT players.name, SUM(prizes.l_prize) AS total_l_prizes,
SUM(prizes.s_prize) AS total_s_prizes FROM prizes, players WHERE
players.playerid = prizes.l_playerid OR players.playerid =
prizes.s_playerid GROUP BY players.playerid
HOWEVER, player gets awarded both l and s prizes for each record even
if he/she only appears as l OR s player for that record.
+-------+----------------+----------------+
| name | total_l_prizes | total_s_prizes |
+-------+----------------+----------------+
| Bob | 1650 | 660 |
| Jay | 1650 | 600 |
| Suzie | 95 | 125 |
| Rick | 350 | 205 |
+-------+----------------+----------------+
Thanks,
Rey