Mysql JOIN (multiple) tables

Question

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

You're looking for UNION, take a look at this post stackoverflow.com/questions/14864933/mysql-join-multiple-tables — ryanbwork
– ryanbwork, Commented Feb 13, 2013 at 23:05

Evaldas Dzimanavicius · Accepted Answer · 2013-02-14 15:42:17Z

4

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

edited Feb 14, 2013 at 15:42

answered Feb 13, 2013 at 23:05

Evaldas Dzimanavicius

6457 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Domi Over a year ago

Is it possible to use LIMIT in query like this (in order to get only latest entry from each table)?

Evaldas Dzimanavicius Over a year ago

Yes, all of the standard operations are possible (limit, order, group). Check my updated answer

Domi Over a year ago

Thanks for update, but is it possible to use LIMIT (and ORDER BY) to 'grab' only the latest row from Table 2 and Table 3 and then JOIN them with Table 1 just like you did in your first query?

Evaldas Dzimanavicius Over a year ago

Yes, you can use Order By, Group By, Limit in the queries that you are union joining afterwards. Check third query example.

Domi Over a year ago

Thanks, you've been very helpful! Just needed to place SELECT clauses inside parentheses. Query works great!

Jan-Henk · Accepted Answer · 2013-02-13 23:05:53Z

0

You can use the UNION statement:

(
    SELECT Name, Temperature, Pressure
    FROM Table1 INNER JOIN Table2 ON Table1.Name = Table2.Name
)
UNION
(
    SELECT Name, Temperature, Pressure
    FROM Table1 INNER JOIN Table3 ON Table1.Name = Table3.Name
)

answered Feb 13, 2013 at 23:05

Jan-Henk

4,8841 gold badge26 silver badges38 bronze badges

Collectives™ on Stack Overflow

Mysql JOIN (multiple) tables

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related