SQLite3 do a SELECT, order by multiple cross-table columns

Question

I'm trying to produce a "history/logs page" out of 3 tables, tables looks like this:

TableA

CreationDate
ModifDate
LastAccessedDate
FileName

TableB

Guid_TableA
CreationDate
ModifDate
LastAccessedDate
FileName

TableC

Guid_TableA
LastModifDate
FileName

FWIW, despite the similarities in columns names, there's no relation between dates in these tables. The only valid column that related between them is Guid_TableA

What I want is...

Select records & order them by date. I'm trying to build an event log, so returning duplicated entries like this is valid (as long as results are ordered correctly by dates)

(TableA)  file-1.txt    '01/01/2012 00:00:00' (CreationDate)
(TableA)  file-1.txt    '01/01/2012 00:00:01' (ModifDate)
(TableB)  file-2.txt    '01/01/2012 00:00:02' (CreationDate)
(TableA)  file-1.txt    '01/01/2012 00:00:03' (LastAccessedDate)

Hopefully my intention is clear; I basically want to select records ordered by date (that is, ordered against multiple cross-tables columns, including duplicated entries)

My question is: Is this possible? If so, how?

Answer 1

You want to use the UNION ALL operator to merge the results of several selects:

SELECT FileName, CreationDate AS Date FROM TableA
UNION ALL SELECT FileName, ModifDate FROM TableA
UNION ALL SELECT FileName, LastAccessDate FROM TableA
UNION ALL SELECT FileName, CreationDate FROM TableB
-- ...etc
ORDER BY Date;

If you want those "TableA" and "CreationDate" annotations, you can add those as well, as literal strings:

SELECT 'TableA', FileName, CreationDate AS Date, 'CreationDate' FROM TableA
UNION ALL SELECT 'TableA', FileName, ModifDate, 'ModifDate' FROM TableA
UNION ALL SELECT 'TableA', FileName, LastAccessDate, 'LastAccessDate' FROM TableA
UNION ALL SELECT 'TableB', FileName, CreationDate, 'CreationDate' FROM TableB
-- ...etc
ORDER BY Date;

Which gives me:

TableA|file-1.txt|01/01/2012 00:00:00|CreationDate
TableA|file-1.txt|01/01/2012 00:00:01|ModifDate
TableB|file-2.txt|01/01/2012 00:00:02|CreationDate
TableA|file-1.txt|01/01/2012 00:00:03|LastAccessDate