SQLite Sort across columns across tables

Question

I have about 8 tables in my SQLite Database, each having a field called time which is time when data is inserted in the table.

I want to perform JOIN on this time column such that data is ordered by time ascending across all tables. If theres an exact match of this time in another table that record should be displayed on the same row otherwise empty.

It sounds like you are heading towards an OUTER join, which SQLite does not support — Karakuri, Nov 12 '14 at 03:40
try using a nested select then from there sort your time. check it [here](http://sqlblog.com/blogs/rob_farley/archive/2011/10/04/joins-without-join.aspx) — KaHeL, Nov 12 '14 at 03:55

score 1 · Accepted Answer · edited May 23 '17 at 12:27

It would be possible to implement full outer joins with compound queries, but for eight tables, this would become rather complex.

Assuming that the time values are unique, it is possible to concatenate all tables while using NULL for the columns that would come from other tables, then use GROUP BY to get one output row per timestamp, and use something like MAX() to pick out the non-NULL values:

SELECT time, MAX(Name1), MAX(Name2), MAX(Name3), ...
FROM (SELECT time, Name1, NULL AS Name2, NULL AS Name3, ... FROM Table1
      UNION ALL
      SELECT time, NULL,  Name2,         NULL,          ... FROM Table2
      UNION ALL
      SELECT time, NULL,  NULL,          Name3,         ... FROM Table3
      ...)
GROUP BY time
ORDER BY time

SQLite Sort across columns across tables

1 Answers1