Question 227071 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Data from two tables filtered by a column

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
Tags:

2

Answers


  1. 0

    You can try this below Query

    SELECT t1.userId, t1.name, t2.level, t2.score
FROM myTab1 AS t1
JOIN myTab2 AS t2 ON t1.userId = t2.userId
WHERE (SELECT COUNT(*) FROM myTab2 WHERE userId = t2.userId AND level >= t2.level) <= 3
ORDER BY t2.level DESC, t2.score DESC;

    subquery I have added (SELECT COUNT(*) FROM myTab2 WHERE userId = t2.userId AND level >= t2.level) can calculates the rank of each user’s level in myTab2.

    Let me know if this is exactly what you wanted.

    Login or Signup to reply.
  2. 0

    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

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search