从2个表中拉出以收集仅匹配项的结果

Question

I have 2 tables in my SQL panel. I have one that appears like this

This one is called games:

ID | Game | Season
 1   MKX2     3
 2   GMR      4
 3   GH4      3

Then I have one for users signed up for a game called game_stats:

ID | User    | GID | Season | Points
 1   Jordan     1      2        10
 2   Jordan     1      3        15
 3   Jordan     3      3        25
 4   Grape      2      1        16
 5   Grape      2      4        17

How can I pull a user's most recent game_stats for the current season? So let's say I want to grab all of Jordan's most reason Game stats. However, I want to only grab the rows that match the game's season. So for example, If I grabbed Jordan I'd want these rows:

ID | User    | GID | Season | Points
 2   Jordan     1      3        15
 3   Jordan     3      3        25

So, ID 1 from game_stats is left out because there was no season matching the number "2" on the original games table. This would give me his current season if he is signed up for it.

How can I combine tables to do this?

Answer 1

You can use INNER JOIN

SELECT a.ID,
       a.USER,
       a.GID,
       a.Season,
       a.Points
FROM   game_stats a
       JOIN games b
         ON a.GID = b.ID
            AND a.Season = b.m_season 

or you can use Exists

SELECT ID,
       USER,
       GID,
       Season,
       Points
FROM   game_stats a
WHERE  EXISTS (SELECT 1
               FROM   game_stats b
               WHERE  a.GID = b.ID
                      AND a.Season = b.m_season)