SQL join data from multiple tables with MYSQL

Question

I have multiple tables and the column fields and all match data type and column number wise, and all have a time field that also matches in the format of YYYY-MM-DD HH:MM:SS across all.

Data Issue

I may have tables where there is a date and time stamp with a matching value in that table's value column, but in the other table it'll not have that same date and time stamp since each table only logs a time when a value fromt he other field is generated.

My Dilemma

I need to join all these tables (like 20 or so) so that their time and value fields show with each value from each table having a separate name.

I also need to show the time for each entry but I need to have just one Time field for all and the other values being Null if it doesn't exist in those tables for that time.

---

Example Tables

Table A

+---------------------+-------+
| Time                | Value |
+---------------------+-------+
| 2016-12-11 00:00:15 | 15    |
| 2016-12-11 00:10:10 | 16    |
| 2016-12-11 00:12:00 | 17    |      
+---------------------+-------+

Table B

+---------------------+-------+
| Time                | Value |
+---------------------+-------+
| 2016-12-11 00:01:15 | 25    |
| 2016-12-11 00:11:10 | 26    |
| 2016-12-11 00:11:00 | 27    |      
+---------------------+-------+ 

TableC

+---------------------+-------+
| Time                | Value |
+---------------------+-------+
| 2016-12-11 00:02:15 | 35    |
| 2016-12-11 00:20:10 | 36    |
| 2016-12-11 00:21:00 | 37    |      
+---------------------+-------+

Expected Result

+---------------------+-----------+-----------+-----------+
| Time                | Value_tba | Value_tbb | Value_tbc |
+---------------------+-----------+-----------+-----------+
| 2016-12-11 00:00:15 | 15        | Null      | Null      |
| 2016-12-11 00:10:10 | 16        | Null      | Null      |
| 2016-12-11 00:12:00 | 17        | Null      | Null      |
| 2016-12-11 00:01:15 | Null      | 25        | Null      |
| 2016-12-11 00:11:10 | Null      | 26        | Null      |
| 2016-12-11 00:11:00 | Null      | 27        | Null      |
| 2016-12-11 00:02:15 | Null      | Null      | 35        |
| 2016-12-11 00:20:10 | Null      | Null      | 36        |
| 2016-12-11 00:21:00 | Null      | Null      | 37        |
+---------------------+-----------+-----------+-----------+

---

My Solution with Two Tables

If I'm using MYSQL is the only or simplest way to do this with left join and then a UNION or UNION ALL to get this to populate as I'm expecting or is this even possible with an SQL statement to get this result? I have an example below of what I'm using for two tables, but when I need to throw additional tables in the loop, I'm not getting the expected results.

Here's what I'm using with two tables which seems to get me what I need but then when I need to add other tables into the mix, this is where I'm getting confused to put all into the same select with additional LEFT JOINs or if I need to populate them separately and then UNION ALL.

SELECT a.Time, a.Value Value_tba, b.value Value_tbb
FROM TableA a
LEFT JOIN TableB b ON b.Time=a.Time

UNION ALL

SELECT b.Time, a.Value Value_tba, b.value Value_tbb 
FROM TableB b
LEFT JOIN TableA a ON a.Time=b.Time

Answer 1

try the following:

select x.time, a.value as value_tba, b.value as value_tbb, c.value as value_tbc from (select time from a union select time from b union select time from c) x left join a on x.time = a.time left join b on x.time = b.time left join c on x.time = c.time