CODEWITHSUNDEEP
mysqlsqldatabasepostgresql
Chron Bag
Chron Bag

Reputation: 587

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?

Upvotes: 3

Views: 843

Answers (2)

Jorge Campos
Jorge Campos

Reputation: 23381

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 points DESC 
 LIMIT 25

Upvotes: 1

EoinS
EoinS

Reputation: 5482

Try making your query the sub-query. I joined the users table to your query:

SELECT p.username, q.points from(
(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
) q 
LEFT JOIN users_table p
ON p.id = q.id

The above is to demonstrate how to extract the information you want from the query you already built.

I hope that helps explain what is happening with this query combining the above (this is more concise):

SELECT p.username, q.points 
FROM leaderboard q 
LEFT JOIN users_table p
ON p.id = q.id
WHERE roundtime BETWEEN LOCALTIMESTAMP - INTERVAL '24 hours' AND    LOCALTIMESTAMP
GROUP BY id 
ORDER BY points 
DESC LIMIT 25

Here is an example of this query

Upvotes: 1

Related Questions