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

table and result clearification


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

desired result (name, total_l_prizes, total_s_prizes)
Bod - 1000 - 595
Jay - 800 - 355
Suzie - 95 - 125
Rick - 350 - 0

1st attempt result (name, total_l_prizes, total_s_prizes) - incorrect
Bod - 1650 - 600
Jay - 1650 - 600
Suzie - 95 - 125
Rick - 350 - 205

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

SELECT players.name,
SUM(case when prizes.l_playerid = players.playerid then prizes.l_prize else 0 end) AS total_l_prizes,
SUM(case when prizes.s_playerid = players.playerid then prizes.s_prize else 0 end) AS total_s_prizes
FROM prizes , players
WHERE players.playerid = prizes.l_playerid OR players.playerid = prizes.s_playerid
GROUP BY players.name
ORDER BY players.name