Sql

Question

I have 3 matrix
Athletes(idathletes, name, age, country)
Coach(idcoach, name, age, country)
Training(idtraining, salary, athletes_idathletes, coach_idcoach)... I want to show the max sum of the coach and his name.... One coach can train more than one athletes

Answer 1

SELECT
c.`name`,
sum(t.`salary`) AS 'balance'
FROM `Coach` c
JOIN `Training` t ON t.`coach_idcoach` = c.`id`
WHERE c.`idcoach` = 1

change the id in last line

Answer 2

SELECT c.name,
sum(t.salary) "sum_salary"
FROM Coach as c
JOIN Training as t ON c.idcoach = t.coach_idcoach
GROUP BY c.name
ORDER BY "sum_salary" DESC;
The first result in output will have Max salary.

Answer 3

This prints me the sum of all coaches... I want only the sum of one coach

Answer 4

It prints the sum of only 1 coach(idcoach = 1). Jason Papastavrou Try it and see for yourself

Answer 5

Yeah you are rigtht... But if i dont know in which idcoach place is he... What should i do?

Answer 6

Jason Papastavrou
You have to know the id of the coach you are searching for because idcoach is the primary key

Answer 7

Ok and one last question, if i want to show the names of the athletes he trains

Answer 8

You can chain different JOIN statements

SELECT
c.`name` AS 'Coach',
group_concat(a.`name`) AS 'Athletes'
sum(t.`salary`) AS 'balance'
FROM `Training` t
JOIN `Coach` c ON t.`coach_idcoach` = c.`idcoach`
JOIN `Athletes` a ON t.`athletes_idathletes` = a.`idathletes`
WHERE c.`id` = 1

Answer 9

You are the best

Answer 10

Jason Papastavrou thanks