logicmonkey - 1 year ago 58
MySQL Question

# select from tables with different numbers of rows

I'm hoping there is a simple answer to this. Competitors race over a series of 3 races. Some competitors only show up for one race. How could I show a final result for ALL competitors?

race 1

``````+------+--------+
| name | result |
+------+--------+
| Ali  |     30 |
| Bob  |     28 |
| Cal  |     26 |
+------+--------+
``````

race 2

``````+------+--------+
| name | result |
+------+--------+
| Ali  |     32 |
| Bob  |     31 |
| Dan  |     24 |
+------+--------+
``````

race 3

``````+------+--------+
| name | result |
+------+--------+
| Eva  |     23 |
| Dan  |     25 |
+------+--------+
``````

The final result should look like this:

``````+------+--------+--------+--------+
| name | result | result | result |
+------+--------+--------+--------+
| Ali  |     30 |     32 |        |
| Bob  |     28 |     31 |        |
| Cal  |     26 |        |        |
| Dan  |        |     24 |     25 |
| Eva  |        |        |     23 |
+------+--------+--------+--------+
``````

The problem I have is with ordering by name from multiple tables.

Here is the example data:

``````CREATE TABLE race (name varchar(20), result int);
CREATE TABLE race1 LIKE race;
INSERT INTO race1 VALUES ('Ali', '30'), ('Bob', '28'), ('Cal', '26');
CREATE TABLE race2 like race;
insert INTO race2 VALUES ('Ali', '32'), ('Bob', '31'), ('Dan', '24');
CREATE TABLE race3 LIKE race;
INSERT INTO race3 VALUES ('Eva', '23'), ('Dan', '25');
``````

Many thanks!

Here we go !!!

``````select race1.name as name ,race1.result ,race2.result , race3.result from race1 left join race2 on race1.name = race2.name left join race3 on race1.name = race3.name

union

select race2.name as name ,race1.result ,race2.result , race3.result from race2 left join race1 on race1.name = race2.name left join race3 on race2.name = race3.name

union

select race3.name as name ,race1.result ,race2.result , race3.result from race3 left join race1 on race3.name = race1.name left join race2 on race2.name = race3.name
``````

It is working :)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download