mysql中join多个表,Mysql JOIN（多个）表

I have 3 tables. 2 of them are the same (same columns, different data), and the third has some info data about other 2. Database looks like this:

Table 1:

+--------------+

| ID | Name |

+--------------+

| 1 | Table 2 |

| 2 | Table 3 |

+--------------+

Table 2:

+-------------------------------+

| Name | Temperature | Pressure |

+-------------------------------+

| Table 2 | 22 | 1013 |

+-------------------------------+

Table 3:

+-------------------------------+

| Name | Temperature | Pressure |

+-------------------------------+

| Table 3 | 20 | 1009 |

+-------------------------------+

I'm trying to JOIN all into one table, which should look like this:

+-------------------------------+

| Name | Temperature | Pressure |

+-------------------------------+

| Table 2 | 22 | 1013 |

| Table 3 | 20 | 1009 |

+-------------------------------+

Any idea how sql query should look like?

Thanks

解决方案

Try union:

SELECT table1.name, temperature, pressure

FROM table1 inner join table2 ON

table1.name = table2.name

UNION

SELECT table1.name, temperature, pressure

FROM table1 inner join table3 ON

table1.name = table3.name

Edit:

You can make another select from those results, then you can limit, group or order:

SELECT * FROM

(

SELECT table1.name, temperature, pressure

FROM table1 inner join table2 ON

table1.name = table2.name

UNION

SELECT table1.name, temperature, pressure

FROM table1 inner join table3 ON

table1.name = table3.name

) as JoinedTable

LIMIT 0, 1

Edit 2:

To have only one row from each table (table 2 and table 3) you can use limit/group by/order by for each query (assuming you have column date):

SELECT table1.name, temperature, pressure

FROM table1 inner join table2 ON

table1.name = table2.name

ORDER BY date DESC

LIMIT 0, 1

UNION

SELECT table1.name, temperature, pressure

FROM table1 inner join table3 ON

table1.name = table3.name

ORDER BY date DESC

LIMIT 0, 1