Find IDs in table where at least two entries exist with that ID and an additional field is A for one and B for the other entry

Question

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?

Answer 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: