CODEWITHSUNDEEP
androidsortingjoinsqliteandroid-sqlite
Shardul
Shardul

Reputation: 27970

SQLite Sort across columns across tables

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.

Upvotes: 1

Views: 66

Answers (1)

CL.
CL.

Reputation: 180080

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

Upvotes: 1

Related Questions