SELECT Statement Involving Three Different Tables

Question

Note: I was unsure how to title this, if anybody has a better idea feel free to change it.

My database currently has the following tables (some columns and other details have been removed from the output of the commands shown, but they should not be relevant):

webserver=> \d+ team

   Column    |          Type          |                     Modifiers                     | Storage  
-------------+------------------------+---------------------------------------------------+----------
 id          | integer                | not null default nextval('team_id_seq'::regclass) | plain     
 name        | character varying(255) | not null                                          | extended  
Indexes:
    "team_pkey" PRIMARY KEY, btree (id)
    "team_name" UNIQUE, btree (name)
Referenced by:
    TABLE "challengesolve" CONSTRAINT "challengesolve_team_id_fkey" FOREIGN KEY (team_id) REFERENCES team(id)

webserver=> \d+ challenge

       Column       |          Type          |                       Modifiers                        | Storage  
--------------------+------------------------+--------------------------------------------------------+----------
 id                 | integer                | not null default nextval('challenge_id_seq'::regclass) | plain     
 name               | character varying(255) | not null                                               | extended  
 points             | integer                | not null                                               | plain     
Indexes:
    "challenge_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "challengesolve" CONSTRAINT "challengesolve_challenge_id_fkey" FOREIGN KEY (challenge_id) REFERENCES challenge(id)

webserver=> \d+ challengesolve

    Column    |            Type             | Modifiers | Storage  
--------------+-----------------------------+-----------+---------
 team_id      | integer                     | not null  | plain   
 challenge_id | integer                     | not null  | plain   
Indexes:
    "challengesolve_pkey" PRIMARY KEY, btree (team_id, challenge_id)
    "challengesolve_challenge_id" btree (challenge_id)
    "challengesolve_team_id" btree (team_id)
Foreign-key constraints:
    "challengesolve_challenge_id_fkey" FOREIGN KEY (challenge_id) REFERENCES challenge(id)
    "challengesolve_team_id_fkey" FOREIGN KEY (team_id) REFERENCES team(id)

The team and challenge tables should be fairly self explanatory. The challengesolve table is an intersection table indicating which teams have solved which challenges.

In short, I would like to find the three teams that have the most points. I am aware already that I can find the number of points a particular team has with a statement like

SELECT SUM(challenge.points) AS points 
FROM challengesolve 
INNER JOIN challenge
ON challenge.id = challengesolve.challenge_id 
WHERE challengesolve.team_id = 1;

How can I modify this query to find the top three teams?

@D-Shih I would, except the accepted answer has already addressed the issue. Thank you for your interest regardless! — DreamConspiracy, Jun 05 '18 at 01:29

score 3 · Accepted Answer · answered Jun 03 '18 at 14:08

In short, I would like to find the three teams that have the most points.

You could use:

SELECT challengesolve.team_id,SUM(challenge.points) AS points 
FROM challengesolve 
INNER JOIN challenge
ON challenge.id = challengesolve.challenge_id 
GROUP BY challengesolve.team_id
ORDER BY points DESC
LIMIT 3;

SELECT Statement Involving Three Different Tables

1 Answers1