0

I have a game where we store user results in a table, similar to this structure:

game_id, user_id, score, ....

Now our service team would like to find all game_ids where user A and user B played together. Meaning I want to SELECT and GROUP BY game_ids WHERE one entry has user_id = A and the other entry has user_id = B.

What would be a performant way to query this in MySQL?

1 Answer 1

1

find all game_ids where user A and user B played together

SELECT t1.game_id
FROM table t1, table t2
WHERE t1.game_id = t2.game_id
  AND t1.user_id = 'A'
  AND t2.user_id = 'B'

If you need the same for 3, 4 or more users, you can add more table copies, or use

SELECT game_id
FROM table
WHERE user_id IN ('A', 'B', 'more users')
GROUP BY game_id
HAVING COUNT(user_id) = :users_count:

If you want to use users list as a parameter, you can use

SELECT game_id
FROM table
WHERE FIND_IN_SET(user_id, :comma_separated_users_list:)
GROUP BY game_id
HAVING COUNT(user_id) = :users_count:
1
  • Straight forward and works flawlessly, thanks!
    – rewb0rn
    Mar 13, 2019 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.