How to sum up max values from another table with some filtering

I have 3 tables

User Table

Score Table

Course Table

What I’m trying to return is rows for each user to display user id and user name along with the sum of the maximum score per course for that user

In the example tables the output I’d like to see is

Result

The SQL I’ve tried so far is:

select TOP(3) u.Id as User_Id, u.UserName as User_Name, SUM(maxScores) as Total_Score
    from Users as u, 
    (select MAX(s.Score) as maxScores 
         from Scores as s 
         inner join Courses as c
         on s.CourseId = c.Id 
         group by s.UserId, c.Id 
     ) x 
     group by u.Id, u.UserName

I want to use a having clause to link the Users to Scores after the group by in the sub query but I get a exception saying:

The multi-part identifier "u.Id" could not be bound

It works if I hard code a user id in the having clause I want to add but it needs to be dynamic and I’m stuck on how to do this

What would be the correct way to structure the query?

>Solution :

You were close, you just needed to return s.UserId from the sub-query and correctly join the sub-query to your Users table (I’ve joined in reverse order to you because to me its more logical to start with the base data and then join on more details as required). Taking note of the scope of aliases i.e. aliases inside your sub-query are not available in your outer query.

select u.Id as [User_Id], u.UserName as [User_Name]
    , sum(maxScore) as Total_Score
from (
  select s.UserId, max(s.Score) as maxScore 
  from Scores as s 
  inner join Courses as c on s.CourseId = c.Id 
  group by s.UserId, c.Id 
) as x
inner join Users as u on u.Id = x.UserId
group by u.Id, u.UserName;