Complex query - need help (where a OR b, SUM() etc)

[rey]

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

[Aggro]

rey wrote:

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

I tried, but also failed to create such a query. However I'm pretty sure
this would be easier by
a) Making two queries
b) changing table structure for prizes.

Currently prizes seem to contain two information sets for each row. It
would be much more clear if you would have either two tables, one for l
and one for s prize. Or one table where is only 1 id and two prizes,
s_prize and l_prize.

So perhaps something like this:

prizes
+----------+-------+-------+
| playerid |l_prize|s_prize|
+----------+-------+-------+
| 1 | 500 | 0 |
| 2 | 650 | 0 |
| 1 | 500 | 0 |
| 2 | 150 | 0 |
| 3 | 95 | 0 |
| 5 | 350 | 0 |
| 1 | 0 | 350 |
| 1 | 0 | 245 |
| 2 | 0 | 65 |
| 2 | 0 | 85 |
| 3 | 0 | 125 |
| 2 | 0 | 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.playerid
GROUP BY players.playerid;

[Aggro]

rey wrote:

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

I tried, but also failed to create such a query. However I'm pretty sure
this would be easier by
a) Making two queries
b) changing table structure for prizes.

Currently prizes seem to contain two information sets for each row. It
would be much more clear if you would have either two tables, one for l
and one for s prize. Or one table where is only 1 id and two prizes,
s_prize and l_prize.

So perhaps something like this:

prizes
+----------+-------+-------+
| playerid |l_prize|s_prize|
+----------+-------+-------+
| 1 | 500 | 0 |
| 2 | 650 | 0 |
| 1 | 500 | 0 |
| 2 | 150 | 0 |
| 3 | 95 | 0 |
| 5 | 350 | 0 |
| 1 | 0 | 350 |
| 1 | 0 | 245 |
| 2 | 0 | 65 |
| 2 | 0 | 85 |
| 3 | 0 | 125 |
| 2 | 0 | 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.playerid
GROUP BY players.playerid;

[Rey]

Thhank you for your reply!

However, at this time I do not have control over the tables. The tables are
given to me and I must work with them the way they are.
FYI the prizes table looks a little more like this:

prizes
+-------+----------+-----------+-------+-------+
| gameid | l_playerid | s_playerid | l_prize | s_prize |
+-------+----------+-----------+-------+-------+
| 1 | 1 | 1 | 500 | 350 |
| 2 | 2 | 1 | 650 | 245 |
| 3 | 1 | 2 | 500 | 65 |
| 4 | 2 | 2 | 150 | 85 |
| 5 | 3 | 3 | 95 | 125 |
| 6 | 5 | 2 | 350 | 205 |
+-------+---------+------------+-------+-------+

I apologize.

Thanks,
Rey
"Aggro" <sp**********@yahoo.com> wrote in message
news:mv***************@read3.inet.fi...

rey wrote:

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

I tried, but also failed to create such a query. However I'm pretty sure
this would be easier by
a) Making two queries
b) changing table structure for prizes.

Currently prizes seem to contain two information sets for each row. It
would be much more clear if you would have either two tables, one for l
and one for s prize. Or one table where is only 1 id and two prizes,
s_prize and l_prize.

So perhaps something like this:

prizes
+----------+-------+-------+
| playerid |l_prize|s_prize|
+----------+-------+-------+
| 1 | 500 | 0 |
| 2 | 650 | 0 |
| 1 | 500 | 0 |
| 2 | 150 | 0 |
| 3 | 95 | 0 |
| 5 | 350 | 0 |
| 1 | 0 | 350 |
| 1 | 0 | 245 |
| 2 | 0 | 65 |
| 2 | 0 | 85 |
| 3 | 0 | 125 |
| 2 | 0 | 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.playerid
GROUP BY players.playerid;

[Rey]

Thhank you for your reply!

However, at this time I do not have control over the tables. The tables are
given to me and I must work with them the way they are.
FYI the prizes table looks a little more like this:

prizes
+-------+----------+-----------+-------+-------+
| gameid | l_playerid | s_playerid | l_prize | s_prize |
+-------+----------+-----------+-------+-------+
| 1 | 1 | 1 | 500 | 350 |
| 2 | 2 | 1 | 650 | 245 |
| 3 | 1 | 2 | 500 | 65 |
| 4 | 2 | 2 | 150 | 85 |
| 5 | 3 | 3 | 95 | 125 |
| 6 | 5 | 2 | 350 | 205 |
+-------+---------+------------+-------+-------+

I apologize.

Thanks,
Rey
"Aggro" <sp**********@yahoo.com> wrote in message
news:mv***************@read3.inet.fi...

rey wrote:

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

I tried, but also failed to create such a query. However I'm pretty sure
this would be easier by
a) Making two queries
b) changing table structure for prizes.

Currently prizes seem to contain two information sets for each row. It
would be much more clear if you would have either two tables, one for l
and one for s prize. Or one table where is only 1 id and two prizes,
s_prize and l_prize.

So perhaps something like this:

prizes
+----------+-------+-------+
| playerid |l_prize|s_prize|
+----------+-------+-------+
| 1 | 500 | 0 |
| 2 | 650 | 0 |
| 1 | 500 | 0 |
| 2 | 150 | 0 |
| 3 | 95 | 0 |
| 5 | 350 | 0 |
| 1 | 0 | 350 |
| 1 | 0 | 245 |
| 2 | 0 | 65 |
| 2 | 0 | 85 |
| 3 | 0 | 125 |
| 2 | 0 | 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.playerid
GROUP BY players.playerid;

[Aggro]

Rey wrote:

However, at this time I do not have control over the tables. The tables are
given to me and I must work with them the way they are.

Ok, would it then be okay two use two queries? (Unless someone else
knows how to do it with one query.)

[Aggro]

Rey wrote:

However, at this time I do not have control over the tables. The tables are
given to me and I must work with them the way they are.

Ok, would it then be okay two use two queries? (Unless someone else
knows how to do it with one query.)