Data from two tables filtered by a column

August 10, 2023

In MySQL, I have a myTab1 which has columns like userId, name, gender, birthMonth. Here userId is the primary key.
There can be only 1 unique userId in the entire table.

I have another table myTab2 which has columns like userId, level, score.
There is no primary key in this table and it is just for the transactions.

Example:

myTab1

userId	name	gender	birthMonth
abc	name1	Male	January
xyz	name2	Female	March
mno	name3	Male	July

myTab2

userId	level	score
abc	1	10
abc	2	9
abc	3	11
abc	4	10
abc	5	23
xyz	1	11
xyz	2	10
mno	1	8

Now I need only the top 3 users with the highest level along with their name which is in myTab1 as below

userId	name	level	score
abc	name1	5	23
xyz	name2	2	10
mno	name3	1	8

Following is what I wrote but not sure how to get the result like above. I am not good at DB queries and looking for some help.

SELECT 
b.*, 
a.name 
FROM 
myTab1 AS b 
INNER JOIN myTab2 as a ON b.userId=a.userId 
ORDER BY level DESC 
limit 3

>Solution :

You can try this…

SELECT
    t1.userId,
    t1.name,
    MAX(t2.level) AS level,
    MAX(t2.score) AS score
FROM
    myTab1 t1
JOIN
    myTab2 t2 ON t1.userId = t2.userId
GROUP BY
    t1.userId, t1.name
ORDER BY
    level DESC
LIMIT 3;

Output