How to merge unrelated tables, but sort on a common column type?

Ok, for the example, I have three tables:

**Table A**
id (serial)
timestamp (timestamp)
value1 (double)
value2 (double)

**Table B**
id (serial)
timestamp (timestamp)
text1 (text)
char1 (character)

**Table C**
id (serial)
timestamp (timestamp)
int1 (int)
int2 (int)

The id fields are unique to each table, and act as primary keys. Timestamps are entered when rows are placed in the table, but are not connected to the other tables, and each table can have rows inserted at varying times.

What I want is a view, or a single dataset, that contains all the records from each of the tables, sorted by timestamp.

In this example, this would mean that the dataset would have the following columns:

**Output Table**
timestamp (timestamp)
value1 (double)
value2 (double)
text1 (text)
char1 (character)
int1 (int)
int2 (int)

I understand that for each row of this resulting dataset, 4 of the columns would be empty. However, I need to be able to view the data from all of the tables sorted in timestamp order (and for a given timestamp range)

I've looked at unions, but they want common column datatypes, so that didn't fit. Joins appeared to need a connection between columns in one table and another, so that didn't fit.

I just need to create a table, made up of all the columns of the three tables, using a single one (timestamp) as a common sorting column.

What would be the best way of going about this? Is this even possible in SQL?

My initial idea was to extract the data from each table separately into an array (PHP/C++), then perform a sort there, but this appears to be incredibly slow, so I was hoping for a much faster SQL solution.

Note: The tables could have many thousand entries each. My database is in PostgreSQL if it is relevant.

Answers


Following code does what you need, and also takes care of different collations between columns. Solution is MySQL specific (due to CAST function being used, and having collation-related problems).

SELECT * FROM (
SELECT
    `timestamp`,
    CAST(`value1` AS CHAR) AS `value1`,
    CAST(`value2` AS CHAR) AS `value2`,
    CAST(NULL AS CHAR) AS `text1`,
    CAST(NULL AS CHAR) AS `char1`,
    CAST(NULL AS SIGNED) AS `int1`,
    CAST(NULL AS SIGNED) AS `int2`
FROM `table_a`
UNION
SELECT
    `timestamp`,
    CAST(NULL AS CHAR) AS `value1`,
    CAST(NULL AS CHAR) AS `value2`,
    CAST(`text1` AS CHAR) AS `text1`,
    CAST(`char1` AS CHAR) AS `char1`,
    CAST(NULL AS SIGNED) AS `int1`,
    CAST(NULL AS SIGNED) AS `int2`
FROM `table_b`
UNION
SELECT
    `timestamp`,
    CAST(NULL AS CHAR) AS `value1`,
    CAST(NULL AS CHAR) AS `value2`,
    CAST(NULL AS CHAR) AS `text1`,
    CAST(NULL AS CHAR) AS `char1`,
    CAST(`int1` AS SIGNED) AS `int1`,
    CAST(`int2` AS SIGNED) AS `int2`
FROM `table_c`) `table_all`
ORDER BY `timestamp`

Also, the fact that you can do it, doesn't mean that you should do it. Better try to rearrange, (de)normalize your data, otherwise you might be running into similar issues over and over again. Sorting many rows in UNION result set is everything but efficient...


You can add the missing columns manually with union:

SELECT * FROM(
    SELECT timestamp, value1, value2, null as text1, null as char1, null as int1, null as int2 FROM tableA
    union all
    SELECT timestamp, null, null, text1, char1, null, null FROM tableB
    union all
    SELECT timestamp, null, null, null, null, int1, int2 FROM tableC
) ORDER BY timestamp;

Need Your Help

mylib.so has text relocations. This is wasting memory and is a security risk. Please fix

android native android-4.4-kitkat

My Android application (using native library) print this warning on Android 4.4 :

Is there any way to link to VSTS 2010 from Delphi XE2?

delphi tfs2010 delphi-xe2 vsts2010

I am working on Delphi XE2 version. We are planning to move our code base from JEDI to VSTS 2010. Is there any way to add plugin (like JEDI) or link VSTS 2010 to Delphi XE2?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.