Let's say I have three tables:
t1:
| client_id | transmission_id | timestamp | column_A |
|---|---|---|---|
| 1 | AAA1 | 2024-10-16 10:31:27 | Banana |
| 1 | AAA2 | 2024-10-16 11:31:27 | Citrus |
| 2 | BBB1 | 2024-10-16 09:12:14 | Apple |
t2:
| client_id | transmission_id | timestamp | column_B |
|---|---|---|---|
| 1 | AAA1 | 2024-10-16 10:41:27 | Paris |
| 1 | AAA2 | 2024-10-16 11:41:27 | London |
| 2 | BBB1 | 2024-10-16 09:22:14 | NY |
t3:
| client_id | transmission_id | timestamp | column_C |
|---|---|---|---|
| 1 | AAA1 | 2024-10-16 10:31:27 | Eat |
| 1 | AAA2 | 2024-10-16 11:31:27 | Pray |
| 2 | BBB1 | 2024-10-16 09:12:14 | Love |
You might notice that while client_id, transmission_id and timestamp (which I previously used as join keys) match for t1 and t3, timestamp data doesn't match in t2.
I'd like to join these tables such that the differences in timestamp data would be unnested into different rows:
| client_id | transmission_id | timestamp | column_A | column_B | column_C |
|---|---|---|---|---|---|
| 1 | AAA1 | 2024-10-16 10:31:27 | Banana | Eat | |
| 1 | AAA1 | 2024-10-16 10:41:27 | Paris | ||
| 1 | AAA2 | 2024-10-16 11:31:27 | Citrus | Pray | |
| 1 | AAA2 | 2024-10-16 11:41:27 | London | ||
| 2 | BBB1 | 2024-10-16 09:12:14 | Apple | Love | |
| 2 | BBB1 | 2024-10-16 09:22:14 | NY |
Unfortunately I'm a bit stumped on how to do that.
I have tried using aliases to map different timestamps to their own columns (as joining the data without the timestamp as key results in ambiguity), such as this:
| client_id | transmission_id | t1_t3.timestamp | t2.timestamp | column_A | column_B | column_C |
|---|---|---|---|---|---|---|
| 1 | AAA1 | 2024-10-16 10:31:27 | 2024-10-16 10:41:27 | Banana | Paris | Eat |
| 1 | AAA2 | 2024-10-16 11:31:27 | 2024-10-16 11:41:27 | Citrus | London | Pray |
| 2 | BBB1 | 2024-10-16 09:12:14 | 2024-10-16 09:22:14 | Apple | NY | Love |
But my wish is to have a single timestamp column as it's difficult to follow the "timeline" beyond this toy example.
You'll only want to join the tables. You need full outer joins in this situation.
select *
from t1
full outer join t2 using (client_id, transmission_id, timestamp)
full outer join t3 using (client_id, transmission_id, timestamp)
order by client_id, transmission_id, timestamp;