I have problems with the following situation. Can this even be done in one query?
I have 3 tables - 'results_of_game' and (less important) 'games' and 'players'.
'results_of_game' table has fields 'game_id', 'player_id', 'position', 'result'.
Now comes the problem:
I want to find all game_id's where:
* player in first position (results_of_game.position=1) has result='A'
* player in second position has result='B'
* all the remaining players have result='C'
(A/B/C are 3 different numeric values). Number of players in every game is an unknown number between 3 and 10 (and if it makes things any easier then table 'games' includes 'number_of_players' field). How to receive list of game_id's that fulfill these requirements?