Geek Answers Handbook

Join 2 tables for a SELECT query?

First ... here are two tables that I created (without unnecessary 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 prerequisites .. these tables contain data for the competitive ladder, where players are compared with each other according to the ordering of points. users_history1 is a table containing records stored in previous seasons. users_ladders1 contains entries from the current season. I am trying to create a page on my site where players are ranked by the midpoints of their previous records and the current record. Here are the basics for stairs 1v1: http://vilegaming.com/league.x/standings1/3

I want to select from a database of two tables of ordered list players depending on their midpoints from my users_ladders1 and users_history1 accounts. I really don't know how to select from two tables in one query, but I will try as far as possible to illustrate.

Using hyphens in all examples, since SO makes this weird.

 SELECT userh-points FROM users-history1 GROUP BY userh-userid ORDER BY (total userh-points for the user)

GROUP BY is required, as some players may have played in several 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 that I can receive data in the form of rows sorted by common points, and, if possible, also divide the common points by the number of unique records for the player, so that I can get the average value.

+4
source share
3 answers

You want to use 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

A subquery is an important part. It will give you one table with the results of both the current and the history tables combined. Then you can select from this table and do COUNT and SUM to get your averages.

My MySQL syntax is pretty rusty, so please excuse me. I did not have the opportunity to run this, so Iā€™m not even sure that it is running, but this should be enough to get you started.

+15
source

If you want to join in a table and want to select a specific column from one table, and in the other table, select all.

eg.

 Table name = test1 , test2

request:

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

if you want to merge with a specific column

request:

 SELECT test1.column1,test1.column2, test2.* FROM test1 ,test2 where test2.column3='(what ever condition u want to pass)'
+1
source 
 Select col1 from test1 where id = '1' union select * from table2

it can also be used to join tables.

-1
source

More articles:


All Articles