0

I've this table called Runks (a Runk is basically like a challenge in this game that I'm making).

Every game can hold 4 users. Thus per round 4 Runks will be created. 1 round will last 24 hours.

At the end of the round the status of these Runks changes.

However I am running into a problem. If one or more of the users neglected to upload Runk in the meantime I need to create an empty Runk for them in the database.

This query: 

SELECT runk_group_id, COUNT(runk_id)
FROM runks
WHERE runk_status = 'ACTIVE'
GROUP BY runk_group_id

Would output this:

enter image description here

This should then result in a next query creating 5 Runks. 1 Runk needs to be created for group_id 32 1 Runk needs to be created for group_id 35 3 Runks need to be created for group_id 44

Also one thing that needs to be taken into is the fact that I need new Runks created with the player ids that have not yet uploaded a Runk.

So if for group 32 player 1, 2 & 3 have already uploaded a Runk... the Runk that will need to be created needs to belong to player 4.

This is what my table looks like:

enter image description here

Rutger Huijsmans
  • 2,330
  • 2
  • 30
  • 67
  • Is there a table that lists all the player numbers? See http://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist?lq=1 for how to write a query that returns all the player numbers in that table that aren't in `runks`. You can then use that in an `INSERT` query to add the missing rows. – Barmar Nov 01 '16 at 11:21
  • Yes there is a Users table listing that information. Great link thanks man I'll try with that. – Rutger Huijsmans Nov 01 '16 at 11:23

1 Answers1

0

For the sake of an answer, here is a simplified example (apologies for the terrible naming...):

CREATE TABLE users (
  user_id int,
);
INSERT INTO users (1), (2), (3);

CREATE TABLE users_list (
  user_id int
);
INSERT INTO users_list values (1), (1), (1), (3);


-- SELECT as shown
SELECT user_id, count(user_id)
FROM users_list
GROUP BY user_id;
+---------+----------------+
| user_id | count(user_id) |
+---------+----------------+
|       1 |              3 |
|       3 |              1 |
+---------+----------------+

-- Incorrect, count includes all an entry even if the left join has nulls
SELECT u.user_id, count(u.user_id)
FROM users u
LEFT JOIN users_list ul ON u.user_id = ul.user_id
GROUP BY u.user_id;

# Gives - WRONG
+---------+------------------+
| user_id | count(u.user_id) |
+---------+------------------+
|       1 |                3 |
|       2 |                1 |
|       3 |                1 |
+---------+------------------+

-- Doesn't include the nulls in the count so we ge the correct answer
SELECT u.user_id, count(ul.user_id)
FROM users u
LEFT JOIN users_list ul ON u.user_id = ul.user_id
GROUP BY u.user_id;

+---------+-------------------+
| user_id | count(ul.user_id) |
+---------+-------------------+
|       2 |                 0 |
|       1 |                 3 |
|       3 |                 1 |
+---------+-------------------+
slc84
  • 61
  • 3