Merge 2 tables for a SELECT query?

Question

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: http://vilegaming.com/league.x/standings1/3

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.

Answer 1

You'll want to use 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 query is the important part. It will give you a single table with the results of both the current and history tables combined. You can then select from that table and do COUNT and SUM to get your averages.

My MySQL syntax is quite rusty, so please excuse it. I haven't had a chance to run this, so I'm not even sure if it executes, but it should be enough to get you started.

Answer 2

If you want to merge to table and you want to select particular column from one table and in another table want to select all.

e.g.

Table name = test1 , test2

query:

SELECT test1.column1,test1.column2, test2.* FROM test1 ,test2

if you want to merge with particular column

query:

SELECT test1.column1,test1.column2, test2.* FROM test1 ,test2 where test2.column3='(what ever condition u want to pass)'