Search code examples
sqlinner-join

SQL inner join, order by timestamp in two tables

How do I order inner join output from the timestamp in two different tables?

| id |      event |                  timestamp |
------------------------------------------------
|  1 |     passed |        2019-06-05 11:55:44 |
|  1 |     failed |        2019-06-09 08:19:35 |


| id |      event |                  timestamp |
------------------------------------------------
|  1 | email_sent |        2019-06-05 11:56:44 |
|  1 | email_sent |        2019-06-09 08:20:35 |

Desired Result:

| id |      event |                  timestamp |
------------------------------------------------
|  1 |     passed |        2019-06-05 11:55:44 |
|  1 | email_sent |        2019-06-05 11:56:44 |
|  1 |     failed |        2019-06-09 08:19:35 |
|  1 | email_sent |        2019-06-09 08:20:35 |

This is what I have so far, however it orders TableA by the timestamp and then TableB by the timestamp, instead of ordering by all timestamps at once:

SELECT TableA.*, TableB.* FROM TableA 
        INNER JOIN TableB ON TableA.id=TableB.id
        WHERE TableA.id='1' ORDER BY TableA.timestamp, TableB.timestamp
Solution

  • You want union all:

    select id, event, timestamp 
from tablea
union all
select id, event, timestamp 
from tableb
order by timestamp;

    You can add where id = 1 to each subquery, if you want to filter the rows.