mysql - Derived joins and aggregate functions throwing errors -

i stuck in query. have 2 tables player , player_attributes player_api_id primary key.

i need find youngest,oldest player , average overall rating of oldest , youngest player.

query write youngest , oldest player:

select player_name, birthday,year(curdate()) - year(birthday) age player  birthday=(select max(birthday) player) or birthday=(select min(birthday) player)  

query average overall rating of players:

select player_api_id, avg(overall_rating) avg_score, (   select player_api_id, overall_rating player_attributes  ) p group player_api_id; 

error while joining:

select player_api_id, avg(overall_rating),min(birthday),max(birthday) avg_score (   select player_api_id, overall_rating player_attributes ) p join (select birthday player) p1 on p.player_api_id=p1.player_api_id group player_api_id; 

i confused now??

there no reason use subqueries select columns. in fact, in mysql, really, bad idea -- because mysql materializes subqueries.

so, do:

select pa.player_api_id, avg(overall_rating) avg_score,        min(p.birthday), max(p.birthday) player_attributes pa join      player p       on pa.player_api_id = p.player_api_id group pa.player_api_id; 

i'm not sure if rest of logic okay. should @ least fix syntax error.