How to join 4 tables for given keys (MYSQL)

user_14607723 0
Viewed 77

I have made a football database and now need to do some queries but need some advice:

So 4 tables:

  • Players

  • Managers

  • Matches

  • Teams

Need to pull results which display:

Top 100 goal scorers

Player(name) | Team(name) | Matches(goals) | Manager(name)

How would the code look like if:

  • Players table contains; playerid, managerid, teamid, name

  • Managers contains; managerid, name

  • Matches contains; matchid, playerid, goals

  • Teams contains; teamid, name

Right now I have something alone the lines of:

"SELECT players.name, teams.name, matches.goals, managers.name
          FROM players
          INNER JOIN ????
          ORDER BY matches.goals";

So what do I put within the INNER JOIN assuming everything else is correct?

Thanks!

subquery inner-join sum mysql
3 Answers

Use INNER JOIN to join the rows from tables ON matching keys.

  • each JOIN combines the records of two tables
  • ON specifies the columns that should match
  • INNER means, join only where records match

Use GROUP BY to group (aggregate) your selected (joined) records.

  • in the GROUP clause list all columns that should be grouped (different values in result only)
  • in the select-list you can specify an optional aggregated column using an aggregate-function, like SUM(goals) to aggregate column goals as sum

The ORDER BY with a column (-list) and direction sorts the result-set (before limitting).

At the end the LIMIT keyword followed by a number of results cuts your result-set (suitable for TOP x queries). You may observe missing syntax-highlighting in formatted SQL code below. That's due to the fact that this limiting-keyword is not part of ANSI-SQL standard.

SELECT
  player.name as player,
  team.name as team,
  manager.name as manager,
  SUM(match.goals) as scored_goals
FROM Matches match
  INNER JOIN Players player ON match.playerid = player.playerid
  INNER JOIN Managers manager ON player.managerid = manager.managerid
  INNER JOIN Teams team ON player.teamid = team.teamid
ORDER BY match.goals DESC
GROUP BY player.name, team.name, manager.name
LIMIT 100;

Note: the aggregate scored_goals is the sum of all goals for a specific combination, that a given player has scored with the given team under given manager.

I added aliases with as for the columns (otherwise you could probably see multiple ambiguous labeled columns name in the output).

user_57302790

You can join, and aggregate - or just join, and use a subquery to count the goals:

select p.name as player_name, t.name as team_name, m.name as manager_name, 
    (select sum(g.goals) from matches g where g.playerid = p.playerid) cnt_goals
from players p
inner join managers m on m.managerid = p.managerid
inner join teams t on t.teamid = p.teamid
order by cnt_goals desc limit 100
user_106767160 
SELECT P.name, 
       T.name, 
       MA.goals, 
       M.name 
FROM   matches MA 
       INNER JOIN players P 
               ON MA.playerid = P.playerid 
       INNER JOIN managers M 
               ON P.managerid = M.managerid 
       INNER JOIN teams T 
               ON P.teamid = T.teamid 
ORDER  BY MA.goals DESC 
LIMIT  100;
user_131218790
Related