Chron Bag Chron Bag - 6 months ago 9
MySQL Question

Replace id foreign key with username in query result

I have this query which works:

SELECT id, SUM(points) AS points
FROM leaderboard
WHERE roundtime BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND LOCALTIMESTAMP
GROUP BY id ORDER BY points DESC LIMIT 25


It might return something like this:

id points
------------
15 430
17 278
16 92


However, instead of having id in the results, I want to replace it with the username that id corresponds with (from the users table)

Users table:

id username
-------------
15 Frank
16 Joe
17 Andy


So that the first query results in:

username points
----------------
Frank 430
Andy 278
Joe 92


I tried using subqueries but couldn't quite get it working and it was getting messy. Is there a good way of doing this?

Answer

You just need a Plain JOIN to do what you need. Don't use subqueries it will make your SQL slower than it needs to be:

SELECT u.username, SUM(l.points) AS points
  FROM leaderboard l
        INNER JOIN yourUserTable u
                ON l.id = u.ColumnNameForTheUserId
 WHERE roundtime BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' 
                     AND LOCALTIMESTAMP
 GROUP BY u.username 
 ORDER BY l.points DESC 
 LIMIT 25
Comments