Found ranking position for user

Question

I have a these tables

USERS

+----------+---------------+
| id_users |   usr_email   |
+----------+---------------+
| 1        | a@domain.com  |
| 2        | b@domain.com  |
| 3        | c@domain.com  |
| 4        | d@domain.com  |
| 5        | e@domain.com  |
+----------+---------------+

RANKING

+-------------+-----------+----------+
| id_ranking  | id_users  |  points  |
+-------------+-----------+----------+
| 50          | 1         | 27       | //3rd
| 51          | 2         | 55       | //1st
| 52          | 3         | 9        | //5th
| 53          | 4         | 14       | //4th
| 54          | 5         | 38       | //2nd
+-------------+-----------+----------+

I would like to retireve user's data along with it's ranking position, filtering by e-mail. So for example if I want info for mail c@domain.com I should get

+----------+--------|---------------+
| id_users | points | rank_position |
+----------+--------|---------------+
| 3        | 9      | 5             |
+----------+--------|---------------+

I've found this piece of query that returns the ranking position

SELECT x.id_users, x.position
FROM (
    SELECT t1.id_ranking, t1.id_users, @rownum := @rownum + 1 AS position
    FROM ranking t1
    JOIN (SELECT @rownum := 0) r ORDER BY t1.points desc
) x
WHERE x.id_users = 3

But I can't manage to use it in my old query

select u.*, r.points
from users u 
left join ranking r on r.id_users = u.id_users
where u.usr_email = 'c@domain.com'

My attemp

select u.*, r.points, p.*
from users u 
left join ranking r on r.id_users = u.id_users,

(SELECT x.id_users, x.position
FROM (
    SELECT t1.id_ranking, t1.id_users, @rownum := @rownum + 1 AS position
    FROM ranking t1
    JOIN (SELECT @rownum := 0) r ORDER BY t1.points desc
) x
 WHERE x.id_users = u.id_users) p

where u.usr_email = 'c@domain.com'

Any help?

Answer 1

You are missing a join condition. But also, the outer join to ranking is not necessary. You can "remember" the points in the subquery:

select u.*, r.points, r.position
from users u left join
     (select r.*, @rownum := @rownum + 1 AS position
      from ranking r CROSS JOIN
           (SELECT @rownum := 0) r
      order by r.points desc
     ) r
     ON r.id_users = u.id_users
where u.usr_email = 'c@domain.com'