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

players_mode_sumo

players_mode_boxing

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.

CodePudding user response：

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

CodePudding user response：

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;