0

I have 3 tables named users, achievements, and userAchievements. The users table contains information like username, password, e-mail, etc... and the achievements table contains a list of possible achievements and how many points they are worth, and userAchievements lists all achievements completed by all users.

users                        userAchievements           achievements
--------------------------   ------------------------   ------------
username | email             username | achID           achID | points
johnb     [email protected]       johnb      ach001          ach001  5
daver     [email protected]     johnb      ach002          ach002  10
miket     [email protected]   daver      ach001          ach003  15

In my app, once the user is logged in, some information about them from the users table is displayed along with their total achievement points. I'm doing this using two SELECT queries, one to pull information from users, the other to sum the points of all the achievements they have completed. In order to cut it down to one SELECT query, I'm thinking of adding an "achievementTotal" column to users which gets added to by the point value every time an entry in userAchievements is made. Is it possible to get everything I need with just one SELECT query without doing that? Or is there an even better way to implement what I am trying to do?

3
  • Not possible through one select (more precisely not through one command). But check triggers Commented Feb 21, 2020 at 4:05
  • Why both johnb and daver have the same ach001 in userAchievements?
    – Akina
    Commented Feb 21, 2020 at 4:54
  • It sounds like you are describing this site, where users have badges and reputation points. If you're looking for implementation tips, the Stack Exchange database is available through both the Data Explorer, and a full download.
    – AMtwo
    Commented Feb 21, 2020 at 11:58

1 Answer 1

1
SELECT username, email, achID, SUM(points)
FROM users                        
NATURAL JOIN userAchievements           
NATURAL JOIN achievements
WHERE username = 'johnb'
GROUP BY username, email, achID
WITH ROLLUP
HAVING GROUPING(username) + GROUPING(email) + GROUPING(achID) IN (0, 3)

fiddle

is there a way to get the output like this? 3 columns "username | email | SUM(points)" with 1 row of "johnb | [email protected] | 15"

SELECT username, email, SUM(points)
FROM users                        
NATURAL JOIN userAchievements           
NATURAL JOIN achievements
WHERE username = 'johnb'
GROUP BY username, email;
4
  • Thanks, that is really close to what I'm looking for! I am going to have to research all of those terms. I was wondering though, is there a way to get the output like this? 3 columns "username | email | SUM(points)" with 1 row of "johnb | [email protected] | 15".
    – BladePoint
    Commented Feb 21, 2020 at 11:45
  • @BladePoint Of course. Added.
    – Akina
    Commented Feb 21, 2020 at 11:51
  • Thank you! You really earned your checkmark!
    – BladePoint
    Commented Feb 21, 2020 at 11:55
  • @RickJames 1? 2?
    – Akina
    Commented Feb 26, 2020 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.