MySQL select inner join and union

I have this two tables. I have current player id_player, I want to select all games from this player (id_player) and his opponents info. Current player and opponent player are connected with same id_game. or

tables

table game:`id_game` (...)
table game_player:`id_game_player, id_player, id_game`(...)

"give me all separate game info from current player and his opponent and join info for their game (grouped by game)"

result as:

      +---player game info
game1 |
      +---opponent game info

      +---player game info
game2 |
      +---opponent1 game info

      +---player game info
game2 |
      +---opponent2 game info

Can anyone provide me MySQL solution?

Thanks


EDIT: I try with this code but I'm not sure this is fastest way (WHERE IN) and is there any way to group results like above?

SELECT * FROM game_player
    JOIN `game` ON game.id_game = game_player.id_game
    WHERE game_player.id_game
    IN (
    SELECT game_player.id_game
    FROM `game_player`
    WHERE game_player.id_player =2)


ANSWERS:


give this a try

SET @player_ID = 0;                  -- <<== the playerID
SELECT  a.*, b.*, 'player' Status         -- this is the first part of the union
FROM    game a                            -- which gets all the game of
        INNER JOIN game_player b          -- the selected player and its info
            ON a.id_game = b.id_game
WHERE   id_player = @player_ID 
UNION ALL
SELECT  d.*, c.*, 'opponent' status
FROM    game_player c
        INNER JOIN
        (
            SELECT  a.*
            FROM    game aa
                    INNER JOIN game_player bb
                        ON aa.id_game = bb.id_game
            WHERE   bb.id_player = @player_ID
        ) d ON c.id_game = d.id_game AND c.id_player <> @player_ID
ORDER   BY id_game, status


 MORE:


 ? MySQL Union (or similar) query
 ? Mysql UNION with dynamic query
 ? MYSQL: UNION results between two tables where omitting records from first table if PK found in second table
 ? How can I leverage an ORM for a database whose schema is unknown until runtime?
 ? How can I leverage an ORM for a database whose schema is unknown until runtime?
 ? How can I leverage an ORM for a database whose schema is unknown until runtime?
 ? Which ORM can preserve the correspondence between db schema and domain model?
 ? how to minimize application downtime when updating database and application ORM
 ? Can we use MongoDB with ORMs we used to use with relational databases, such as linq2sql, entity framework, subsonic,...?
 ? How to use multilanguage database schema with ORM?