CODEWITHSUNDEEP
mysql
Ally
Ally

Reputation: 23

MySQL query with matching and sum - League Table

I have 5 tables:

as follows

enter image description here

I want to make a league table like this

enter image description here

I am confused how to sum up the scores for each player. This is my code so far:

SELECT Players.name, Teams.name, Results.position FROM Players
INNER JOIN Teams ON Players.team_id = Teams.id
JOIN Results ON Player.id = Results.player_id
JOIN Scores ON Results.position = Scores.scores

Any idea?

Upvotes: 1

Views: 123

Answers (2)

Juan Pablo Califano
Juan Pablo Califano

Reputation: 12333

To list all the players (whether they appear on the results table or not) you have to use a LEFT JOIN.

SELECT p.name player_name,t.name team_name,SUM(COALESCE(s.score,0)) as total_score
FROM players p
INNER JOIN teams t ON t.id = p.team_id
LEFT JOIN results r ON p.id = r.player_id
LEFT JOIN scores s ON s.position = r.position
GROUP BY p.id
ORDER BY total_score desc

Upvotes: 0

juergen d
juergen d

Reputation: 204756

SELECT Players.name, 
       Teams.name,
       sum(Results.position * Scores.scores) as scores
FROM Players
JOIN Teams ON Players.team_id = Teams.id
JOIN Results ON Player.id = Results.player_id
JOIN Scores ON Results.position = Scores.scores
GROUP BY Players.name, Teams.name

Upvotes: 2

Related Questions