I have a couple of tables that need to be be joined. The tables are:
players
- first_name
- last_name
- gender
- location_id
- (additional irrelevant columns)
and
score_entries
- score
- datetime
- player_id
- (additional irrelevant columns)
I need to join them based on player_id
and only want to select records where the players.location_id
is the greatest. So something like this:
SELECT
players.first_name,
players.last_name,
players.gender,
score_entries.score,
score_entries.datetime as jump_date
players.location_id,
FROM score_entries
LEFT JOIN players
ON score_entries.player_id = players.id
WHERE gender="m"
AND location_id="***(ONLY THE HIGHEST LOCATION_ID VALUE IN THE TABLE)***"