Simplify query

[esiason14]

Hi,

Is there a way to simplify this query?

 

SELECT * FROM mlbplayers p, mlbhstats hs, mlbteams t, mlbpositions pos, mlbplayer_positions ppos

WHERE ppos.position_id=6

AND p.player_id=hs. player _id

AND ppos. player =p. player _id

AND hs.mlbteam_id=t.mlbteam_id

AND hs.year = 2007

GROUP BY p.player_id

ORDER by hs.hr DESC

LIMIT 0,10;

 

[fenway]

Well, if you used proper join syntax, it might be easier to follow and maintain.  And * and GROUP BY don't mix. 

 

[sigh... why isn't ONLY_FULL_GROUP_BY the default... oh, wait, I know why... ignore me]

[AmandaF]

There's a pretty good join tutorial here:  http://www.phpfreaks.com/tutorial/data-joins-unions

 

You want something similar to:

 

select p.*, hs.*, t.*, pos.*, ppos.*

from p

join p using player_id

join ppos using player_id

join hs using mlbteam_id

where ppos.position_id = 6

and hs.year = 2007

order by player_id, hr desc

 

Granted, that probably has some errors in it and you'll need to fix the table names, but it should give you a basic idea to start with.  Hope that helps.