sql - How to merge unrelated tables, but sort on a common column type? -
ok, example, have 3 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 unique each table, , act primary keys. timestamps entered when rows placed in table, not connected other tables, , each table can have rows inserted @ varying times.
what want view, or single dataset, contains records each of tables, sorted timestamp.
in example, mean dataset have following columns:
**output table** timestamp (timestamp) value1 (double) value2 (double) text1 (text) char1 (character) int1 (int) int2 (int)
i understand each row of resulting dataset, 4 of columns empty. however, need able view data of tables sorted in timestamp order (and given timestamp range)
i've looked @ unions, want common column datatypes, didn't fit. joins appeared need connection between columns in 1 table , another, didn't fit.
i need create table, made of columns of 3 tables, using single 1 (timestamp) common sorting column.
what best way of going this? possible in sql?
my initial idea extract data each table separately array (php/c++), perform sort there, appears incredibly slow, hoping faster sql solution.
note: tables have many thousand entries each. database in postgresql if relevant.
following code need, , takes care of different collations between columns. solution mysql specific (due cast function being used, , having collation-related problems).
select * ( select `timestamp`, cast(`value1` char) `value1`, cast(`value2` char) `value2`, cast(null char) `text1`, cast(null char) `char1`, cast(null signed) `int1`, cast(null signed) `int2` `table_a` union select `timestamp`, cast(null char) `value1`, cast(null char) `value2`, cast(`text1` char) `text1`, cast(`char1` char) `char1`, cast(null signed) `int1`, cast(null signed) `int2` `table_b` union select `timestamp`, cast(null char) `value1`, cast(null char) `value2`, cast(null char) `text1`, cast(null char) `char1`, cast(`int1` signed) `int1`, cast(`int2` signed) `int2` `table_c`) `table_all` order `timestamp`
also, fact can it, doesn't mean should it. better try rearrange, (de)normalize data, otherwise might running similar issues on , on again. sorting many rows in union result set efficient...
Comments
Post a Comment