help with join

[fnsweet]

Hello everyone,

 

I am hopping someone can help me sort out a query.

 

I have two tables, one table is a players table, and a second table is a player_vitals data that stores heights and weight changes.  I want to list all the players showing the latest height and weight change.

 

this query will not fetch the latest height and weight

 

SELECT a.player_id, a.player_first_name,a.player_last_name, b.player_height, b.player_weight

FROM players a

LEFT JOIN (SELECT vital_id, player_id, player_height, player_weight FROM player_vitals ORDER BY vital_id DESC LIMIT 1) b ON b.player_id = a.player_id

LEFT JOIN teams c using (team_id)

ORDER BY player_last_name, player_first_name

 

 

this query will work only if i tell it what player_id to select from

 

SELECT a.player_id, a.player_first_name,a.player_last_name, b.player_height, b.player_weight

FROM players a

LEFT JOIN (SELECT vital_id, player_id, player_height, player_weight FROM player_vitals WHERE player_id = 6 ORDER BY vital_id DESC LIMIT 1) b ON b.player_id = a.player_id

LEFT JOIN teams c using (team_id)

where a.player_id = 6

ORDER BY player_last_name, player_first_name

 

but i need I need to get my first query to work listing all players, am i missing something?

 

Thanks

[Barand]

When you talk about change in height or weight then a time period usually involved - ie the difference between weight now compared with the known weight at a previous point in time. I see no mention of dates in your queries

[fnsweet]

the vital_id is an auto incro, the max number would be the latest entry for that player.

 

i believe that i have to use group-wise maximum but i cant get it to work correctly

[Barand]

OK, so if the max id for a player is, say, 12345, and his weight in that record is 180lbs, that only tells you his weight changed by 180lb since he was an egg.

[Psycho]

Try

 

SELECT p.player_id, p.player_first_name, p.player_last_name,
       v.player_height, v.player_weight
FROM players p
LEFT JOIN player_vitals v
  ON p.player_id = v.player_id
 AND v.vital_id IN (SELECT MAX(vital_id) FROM player_vitals GROUP BY player_id)
LEFT JOIN teams c using (team_id)
ORDER BY p.player_last_name, p.player_first_name