'Global Elo leadeboard with multiple tables

I'm trying to create elo based leaderboard from multiple tables. Table structures are shown down below. What I am looking for is query that would sum elo from all tables into one for every single player that is in any of the tables and then order it desc to get top 10 players with global elo.

players_mode_thebridges

id name elo
1 JesusChrist69 13
2 62MB 196

players_mode_sumo

id name elo
1 JesusChrist69 196

players_mode_boxing

id name elo
1 62MB 723

Does anyone know how to make that work? I am struggling on that problem for quite some time. All I was able to do was get global elo of one specific player but what I need is to get top 10 players. Thanks in advance for answers.

mysql


Solution 1:[1]

SELECT `name`, SUM(elo) AS elo FROM
(SELECT `name`, `elo` FROM players_mode_boxing
UNION
SELECT `name`, `elo` FROM players_mode_sumo
UNION
SELECT `name`, `elo` FROM players_mode_thebridges) X
GROUP BY `name`
ORDER BY `elo` DESC LIMIT 10

Solution 2:[2]

How I was taught was to join the tables with the pairs for example players_mode_sumo.id=players_mode_sumo.id and a descending order of elo and a limit of 10 plus I add all the elo

SELECT players_mode_sumo.id, players_mode_thebridges.name, SUM(players_mode_thebridges.elo)
FROM players_mode_sumo, players_mode_thebridges, players_mode_boxing 
WHERE players_mode_sumo.id=players_mode_sumo.id 
AND players_mode_sumo.id=players_mode_boxing.id 
ORDER BY elo DESC 
SUM(players_mode_thebridges.elo) 
LIMIT 10;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 RiggsFolly

Related Questions