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 need to sign in to view this answers
Leave feedback about this