Arian Faurtosh Arian Faurtosh - 1 month ago 5
MySQL Question

Combining / Sorting two datetime tables/arrays/lists

I have two MySQL tables, both with Timestamp (DATE/TIME)... I need a query to combine them and sort them together, by timestamp (2013-08-21 13:13:47).

Table 1
---------------------------------------
Timestamp | Col 1 | Col 2 | Col 3


Table 2
---------------------------------------
Timestamp | Col 1 | Col 2 | Col 3 | Col 4


I want to print out

PRINT OUT (in order by timestamp)
----------------
Timestamp | Col 1 | Col 2 | Col 3 | Col 4
Timestamp | Col 1 | Col 2 | Col 3
Timestamp | Col 1 | Col 2 | Col 3
Timestamp | Col 1 | Col 2 | Col 3 | Col 4
Timestamp | Col 1 | Col 2 | Col 3
Timestamp | Col 1 | Col 2 | Col 3 | Col 4
Timestamp | Col 1 | Col 2 | Col 3
Timestamp | Col 1 | Col 2 | Col 3 | Col 4
Timestamp | Col 1 | Col 2 | Col 3 | Col 4


What is the simplest way to do this? Should I put the two tables into an array and then sort? or should I do this with a sql query?

I'm using PHP & MySQL...

Answer

Assuming that Table1.Col1 has the same kind of data as Table2.Col1, Table1.Col2 has the same kind of data as Table2.Col2, etc, then this is probably the easiest way. Tables in a union have to have the same number of columns. I use NULL to take the place of Col4. You might want additional columns in the ORDER BY clause.

select T1.Timestamp, T1.Col1, T1.Col2, T1.Col3, NULL as Col4
from Table1
union all
select T2.Timestamp, T2.Col1, T2.Col2, T2.Col3, T2.Col4
from Table2
order by Timestamp

It makes sense to do this only if the similarly named columns have the same kind of data. If Table1.Col1 contains names, and Table2.Col1 contains phone numbers, it doesn't make much sense to do this.