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?
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)