MAX value of same columns, two tables

[ninedoors]

What I need to get but not sure if it is possible is the MAX value of a column in 2 different tables.  I'll explain a little further.

 

This is for a sports stats query for my site.  I have 2 separate tables for players and goalies that track all the players in my hockey league stats.  In some cases I have a player that plays both goalie and player.  And might be a spare goalie for a different team than he plays out for.  This player has the same playerID number in each table.  There is also a column in each table names games which obviously tracks the number of games this player has played at each position and a teamID column that associates that playerID for each game played with that team.

 

How would I go about retrieving the MAX value out of these to games columns and the teamID associated with that value?

 

So if playerID 183 played 4 games as a player with team 5 and he played 3 games as a goalie for team 6, I would want teamID = 5, games = 4 returned.

 

Can I do this in one query?  I know I can do it in 2 queries but am interested in learning more complex sql to help simplify my code.

 

Thanks for the help.

 

Nick

[rhodesa]

i can't think of something off the top of my head. but it sounds like you should break this out into multiple tables. I'm thinking one for basic player info, another for team/game/position, and a third for stats. that way the team/game/position data would be in one table

[fenway]

Why not just UNION the tables/