sql query for fetching leaderboard from two tables

Question

I have two MySql table user and marks

User table:

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int          | NO   | PRI | NULL    | auto_increment |
| firstName | varchar(255) | YES  |     | NULL    |                |
| lastName  | varchar(255) | YES  |     | NULL    |                |
| email     | varchar(55)  | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

Marks Table

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int          | NO   | PRI | NULL    | auto_increment |
| user_id      | int          | NO   |     | NULL    |                |
| subject_id   | varchar(255) | YES  |     | NULL    |                |
| score        | int          | YES  |     | NULL    |                |
| subject_name | varchar(225) | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

I want to fetch details(userid, firstName and lastName) of top 10 users with the highest marks in descending order. Marks of the user is defined as sum of all scores a user has in different subjects.

I am really confused which join is to be used here, I am new to MySql and this query is kind of challenging for me, Hope you understood the problem. Please let me know if you have any suggestion, Thank You

Answer 1

You should inner join the table Marks on the id from table User with user_id from the table Marks:

SELECT user_id, firstName, lastName 
FROM User
INNER JOIN Marks
ON User.id = Marks.user_id

Here is a helpful resource for SQL joins

Answer 2

You can join the two tables together by id=user_id, then you group the result by id, sort by the total marks per id, then take the top 10 results.

If you wanted a result even if the user had no marks at all, change JOIN to LEFT JOIN, this will still give you a result from the first table even if there are no results from the second.

SELECT u.id, u.firstName, u.lastName, SUM(m.score) AS TotalScore
FROM [User] AS u
JOIN Marks AS m ON m.user_id = u.id
GROUP BY u.id, u.firstName, u.lastName
ORDER BY SUM(m.score) DESC
LIMIT 10;