logicmonkey logicmonkey - 23 days ago 11
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!

Answer

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 :)