First.. here are the two tables I've created (sans irrelevant columns)..
CREATE TABLE users_history1 ( circuit tinyint(1) unsigned NOT NULL default '0', userh_season smallint(4) unsigned NOT NULL default '0', userh_userid int(11) unsigned NOT NULL default '0', userh_rank varchar(2) NOT NULL default 'D', userh_wins int(11) NOT NULL default '0', userh_losses int(11) NOT NULL default '0', userh_points int(11) NOT NULL default '1000', KEY (circuit, userh_userid), KEY (userh_season) ) ENGINE=MyISAM; CREATE TABLE users_ladders1 ( circuit tinyint(1) unsigned NOT NULL default '0', userl_userid int(11) unsigned NOT NULL default '0', userl_rank char(2) NOT NULL default 'D', userl_wins smallint(3) NOT NULL default '0', userl_losses smallint(3) NOT NULL default '0', userl_points smallint(4) unsigned NOT NULL default '1000', PRIMARY KEY (circuit, userl_userid), KEY (userl_userid) ) ENGINE=MyISAM;
Some background.. these tables hold data for a competitive ladder where players are compared against each other on an ordered standings by points. users_history1 is a table that contains records stored from previous seasons. users_ladders1 contains records from the current season. I'm trying to create a page on my site where players are ranked on the average points of their previous records and current record. Here is the main standings for a 1v1 ladder:
I want to select from the database from the two tables an ordered list players depending on their average points from their users_ladders1 and users_history1 records. I really have no idea how to select from two tables in one query, but I'll try, as generic as possible, to illustrate it..
Using hyphens throughout the examples since SO renders it weird.
SELECT userh-points FROM users-history1 GROUP BY userh-userid ORDER BY (total userh-points for the user)
Needs the GROUP BY since some players may have played in multiple previous seasons.
SELECT userl-points FROM users-ladders1 ORDER BY userl-points
I want to be able to combine both tables in a query so I can get the data in form of rows ordered by total points, and if possible also divide the total points by the number of unique records for the player so I can get the average.