2

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)***"
Lion
  • 18,729
  • 22
  • 80
  • 110
user1418227
  • 201
  • 1
  • 4
  • 12

1 Answers1

1

Please try the following query

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 players.location_id = (SELECT MAX(location_id) from players)
Chetter Hummin
  • 6,687
  • 8
  • 32
  • 44