SELECT
    players.firstname   AS firstname,
    players.lastname    AS lastname,
    players.nickname    AS nickname,
    COUNT(gs.game_id)   AS games_played,

	rival_player.nickname AS rival_nickname,
	rival_player.firstname AS rival_firstname,
	rival_player.lastname AS rival_lastname


FROM `games_split` AS gs

INNER JOIN `games_attributes` AS ga1
    ON gs.game_id = ga1.game_id AND ga1.attribute_id = 11

INNER JOIN `players` AS players
    ON gs.player_id = players.player_id

INNER JOIN `players` AS rival_player
	ON gs.rival_player_id = rival_player.player_id

LEFT JOIN `games_attributes` AS ga2
    ON gs.game_id = ga2.game_id AND ga2.attribute_id = 3

LEFT JOIN `games_attributes` AS ga3
    ON gs.game_id = ga3.game_id AND ga3.attribute_id = 4

LEFT JOIN `games_attributes` AS ga4
    ON gs.game_id = ga4.game_id AND ga4.attribute_id = 5

LEFT JOIN `games_attributes` AS ga5
    ON gs.game_id = ga5.game_id AND ga5.attribute_id = 6

	LEFT JOIN `games_attributes` AS ga12
    ON gs.game_id = ga12.game_id AND ga12.attribute_id = 12

GROUP BY gs.player_id, gs.rival_player_id
ORDER BY firstname