First.. listed here are the 2 tables I have produced (sans irrelevant posts)..
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 any competitive ladder where gamers are in comparison against one another with an purchased standings by points. customers_history1 is really a table that consists of records saved from previous seasons. customers_ladders1 consists of records in the current season. I am trying to produce a page on my small site where gamers are rated around the average points of the previous records and current record. This is actually the primary standings for any 1v1 ladder: http://vilegaming.com/league.x/standings1/3
I wish to choose in the database in the two tables an purchased list gamers based on their average points using their customers_ladders1 and customers_history1 records. I truly have no clue how you can choose from two tables in a single query, but I'll try, as generic as you possibly can, as one example of it..
Using hyphens through the good examples since SO renders it strange.
SELECT userh-points FROM users-history1 GROUP BY userh-userid ORDER BY (total userh-points for the user)
Needs the audience BY since some gamers might have performed in multiple previous seasons.
SELECT userl-points FROM users-ladders1 ORDER BY userl-points
I wish to have the ability to mix both tables inside a query in order to obtain the data in type of rows purchased by total points, and when possible also divide the entire points by the amount of unique records for that player in order to obtain the average.
You will want to make use of a UNION SELECT:
SELECT p.id, COUNT(p.id), SUM(p.points) FROM (SELECT userh_userid AS id, userh_points AS points FROM users_history1 UNION SELECT userl_userid, userl_points FROM users_ladders1) AS p GROUP BY p.id
The sub totally the key part. You'll have just one table using the outcomes of both current and history tables combined. After that you can choose from that table and do COUNT and SUM to obtain your earnings.
My MySQL syntax is very rusty, so please excuse it. I've not had an opportunity to run this, so I am not really confident that it executes, but it ought to be enough to enable you to get began.