How do I set points based on whether the person was 1st, 2nd, or 3rd (per game)?

Question

In my 'players' table I have the data of players id, name, and points(currently 0 point to everyone).

My 'games' table includes the game id, game name, 1st_id, 2nd_id, and 3rd_id (all the last 3 ids match with 'players' id from the first table.

Both already have data inserted already.

my plan to the score system was to add points based on how many times a participant was in the top 3.
1st place worth 3 points, 2nd place worth 2 points, 3rd place worth 1 point.

So for example if John won the 1st place in running and 2nd place in ice skating he would have 5 points total.

How do I set the points in the 'players' table to achieve this?

Answer 1

I imagine you'd probably need to run two queries to achieve that.

The first query updates the 'games' table, updating either '1st_id', '2nd_id', '3rd_id' column, or all three of them.

The second query updates the 'players' table, updating the 'points' column - depending on the player's rank (which decides the amount of points earned).

Just my imagination though ; )

Answer 2

You need a programming language to sum the point before updating.

https://www.sololearn.com/learn/SQL/1850/

https://www.sololearn.com/learn/SQL/1869/

something like this:

int p1s = 0,
p2s = 0,
p3s = 0;

array ids = SELECT id FROM players

array 1st = SELECT 1st_id FROM games

for loop id in 1st_id
switch(id)
case ids[0]: p1s+=3;break;
case....

array 2nd = SELECT....
for loop....
switch
.. +=2

array 3rd = SELECT....
for loop....
switch..
.... +=1

UPDATE players SET points = p1s WHERE id = id[0]
UPDATE...
UPDATE....

Answer 3

Thank you both so much! :)