Currently, the table looks like this. This is basically a list of leaderboards entries everything looks fine at this point.
+-------+-----------------+-------+--------------+--------------+--------------+--------------+----------------------+----------+
| entry | challenge_level | proof | player1_name | player2_name | player3_name | player4_name | submission_date | approved |
+-------+-----------------+-------+--------------+--------------+--------------+--------------+----------------------+----------+
| 8 | 52 | url | PLAYER_A | PLAYER_D | PLAYER_B | PLAYER_C | 2018-07-16T16:14:01Z | true |
| 9 | 60 | url | PLAYER_C | PLAYER_K | PLAYER_X | PLAYER_Y | 2018-07-16T16:33:10Z | true |
| 11 | 51 | url | PLAYER_A | PLAYER_B | PLAYER_C | PLAYER_D | 2018-07-16T17:42:13Z | true |
| 13 | 45 | url | PLAYER_C | PLAYER_H | PLAYER_J | PLAYER_D | 2018-07-16T18:26:59Z | true |
| 14 | 61 | url | PLAYER_A | PLAYER_C | PLAYER_I | PLAYER_B | 2018-07-16T21:09:55Z | true |
| 15 | 36 | url | PLAYER_A | PLAYER_C | PLAYER_D | PLAYER_B | 2018-07-17T08:24:37Z | true |
| 16 | 62 | url | PLAYER_E | PLAYER_C | PLAYER_F | PLAYER_G | 2018-07-17T09:37:13Z | true |
+-------+-----------------+-------+--------------+--------------+--------------+--------------+----------------------+----------+
The problem begins when I would like to have my desired result formatted as following, desired result
+-------+-----------------+-------+--------------+----------------------+----------+
| entry | challenge_level | proof | PLAYER_NAME | submission_date | approved |
+-------+-----------------+-------+--------------+--------------+--------------+---+
| 14 | 61 | url | PLAYER_A | 2018-07-16T16:14:01Z | true |
| 14 | 61 | url | PLAYER_B | 2018-07-16T16:33:10Z | true |
| 16 | 62 | url | PLAYER_C | 2018-07-16T17:42:13Z | true |
| 8 | 52 | url | PLAYER_D | 2018-07-16T18:26:59Z | true |
| 16 | 62 | url | PLAYER_E | 2018-07-16T21:09:55Z | true |
| 16 | 62 | url | PLAYER_F | 2018-07-17T08:24:37Z | true |
| 16 | 62 | url | PLAYER_G | 2018-07-17T09:37:13Z | true |
+-------+-----------------+-------+--------------+--------------+--------------+---+
On top of that, there would be one important factor to consider, players can appear multiple times so, for example, PLAYER_A is listed in multiple entries but the result should be the highest challenge_level he did - player name has to be unique and cant appear multiple times in the desired result.
I do not know where to start with this one, any hints will be valuable.
http://sqlfiddle.com/#!9/869812/21
SELECT l.`entry`,
l.`challenge_level`,
l.`proof`,
l_max.player,
l.`submission_date`,
l.`approved`
FROM leaderboards l
INNER JOIN
(SELECT player, MAX(challenge_level) as `level`
FROM (SELECT l1.`entry`,
l1.`challenge_level`,
l1.`proof`,
l1.`player1_name` as player,
l1.`submission_date`,
l1.`approved`
FROM leaderboards l1
UNION ALL
SELECT l2.`entry`,
l2.`challenge_level`,
l2.`proof`,
l2.`player2_name`,
l2.`submission_date`,
l2.`approved`
FROM leaderboards l2
UNION ALL
SELECT l3.`entry`,
l3.`challenge_level`,
l3.`proof`,
l3.`player3_name`,
l3.`submission_date`,
l3.`approved`
FROM leaderboards l3
UNION ALL
SELECT l4.`entry`,
l4.`challenge_level`,
l4.`proof`,
l4.`player4_name`,
l4.`submission_date`,
l4.`approved`
FROM leaderboards l4
) l_all
GROUP BY l_all.player) l_max
ON l.challenge_level = l_max.level
and (l.player1_name = l_max.player
OR l.player2_name = l_max.player
OR l.player3_name = l_max.player
OR l.player4_name = l_max.player)