This question is an extension to a question I've previously asked that was overly simplified. The more accurate example is demonstrated in this SQLFiddle, where I demonstrate a working (but slow) solution, followed by my attempt to adapt the previous answer to the actual problem.
The actual problem comes because the two tables contain events for multiple timelines.
CREATE TABLE foo (ts int, id text, foo text); INSERT INTO foo (ts, id, foo) VALUES (1, 'A', 'Lorem'), (1, 'B', 'ipsum'), (4, 'B', 'dolor'), (5, 'A', 'sit'), (8, 'A', 'amet'), (8, 'B', 'consectetur'); CREATE TABLE bar (ts int, id text, bar text); INSERT INTO bar (ts, id, bar) VALUES (1, 'A', 'adipiscing'), (5, 'B', 'elit'), (6, 'A', 'sed'), (9, 'B', 'do ');
Each table has events for timelines 'A' and 'B'. The goal is to combined the results in to a single result set showing the "state" of each timeline. The two timelines are orthogonal.
ts id foo bar 1 A Lorem adipiscing 5 A sit adipiscing 6 A sit sed 8 A amet sed 1 B ipsum (null) 4 B dolor (null) 5 B dolor elit 8 B consectetur elit 9 B consectetur do