Get all rows from table and sort them by timestamp

Question

I have these test tables which I would like to select and combine the result by timestamp:

create table employees
(
    id              bigint primary key,
    account_id      integer,
    first_name      varchar(150),
    last_name       varchar(150),
    timestamp       timestamp
);

create table accounts
(
    id               bigint primary key,
    account_name     varchar(150) not null,
    timestamp        timestamp
);

create table short_name
(
    account_id       bigint primary key,
    full_name     varchar(150) not null
);

INSERT INTO short_name(account_id, full_name)
VALUES(1, 'city 1');

INSERT INTO short_name(account_id, full_name)
VALUES(2, 'city 2');

INSERT INTO employees(id, account_id, first_name, last_name, timestamp)
VALUES(1, 1, 'Donkey', 'Kong', '10-10-10');

INSERT INTO employees(id, account_id, first_name, last_name, timestamp)
VALUES(2, 2, 'Ray', 'Kurzweil', '11-10-10');

INSERT INTO employees(id, account_id, first_name, last_name, timestamp)
VALUES(32, 2, 'Ray2', 'Kurzweil2', '1-10-10');

INSERT INTO employees(id, account_id, first_name, last_name, timestamp)
VALUES(33, 2, 'Ray3', 'Kurzweil3', '2-10-10');

INSERT INTO employees(id, account_id, first_name, last_name, timestamp)
VALUES(3432, 3, 'Percy', 'Fawcett', '6-10-10');

INSERT INTO accounts(id, account_name, timestamp)
VALUES(1, 'DK Banana Account', '5-10-10');

INSERT INTO accounts(id, account_name, timestamp)
VALUES(2, 'Kurzweil''s invetions moneyz baby!', '10-10-10');

INSERT INTO accounts(id, account_name, timestamp)
VALUES(3, 'Amazonian Emergency Fund', '10-10-10');


select *, e.timestamp, sn.full_name from employees e
    INNER JOIN short_name as sn on sn.account_id = e.id
union all
select *, a.timestamp from accounts a
where timestamp >= '2022-03-25T13:00:00'
and timestamp < '2022-04-04T13:00:00'
AND timestamp IS NOT NULL
order by timestamp;

https://www.db-fiddle.com/f/pwzwQTsHuP27UDF17eAQy4/36

How I can select the tables and display a combined table rows ordered by timestamp? The problem is that I have a different number of table columns and I would like to display them also and globally to sort all rows by timestamp.

Is it possible to display also the name of the tables as a first column into the select result?

Example result for result with table name:

table_name	timestamp
employees	2010-10-10T00:00:00.000Z
accounts	2010-11-10T00:00:00.000Z

Answer 1

As others have mentioned, you haven't given a clear example of what you want the output to be; however, here's my attempt assuming you want one record per employee and one additional record per account.

Each row of the result set contains every possible column. These can be removed/reordered in the final select.

Query

with accounts_and_employees as (
  select 
    'accounts' as table_name, 
    accounts.id, 
    accounts.id as account_id,
    accounts.timestamp, 
    account_name, 
    null as first_name,
    null as last_name
  from accounts
  union
  select 
    'employees' as table_name, 
    employees.id, 
    account_id,
    employees.timestamp, 
    account_name,
    first_name, 
    last_name
  from employees
  join accounts
    on employees.account_id = accounts.id
)
select accounts_and_employees.*, full_name
from accounts_and_employees
left join short_name
  on short_name.account_id = accounts_and_employees.account_id
where timestamp between '2010-01-10' and '2010-10-30'
order by timestamp;

table_name	id	account_id	timestamp	account_name	first_name	last_name	full_name
employees	32	2	2010-01-10T00:00:00.000Z	Kurzweil's invetions moneyz baby!	Ray2	Kurzweil2	city 2
employees	33	2	2010-02-10T00:00:00.000Z	Kurzweil's invetions moneyz baby!	Ray3	Kurzweil3	city 2
accounts	1	1	2010-05-10T00:00:00.000Z	DK Banana Account			city 1
employees	3432	3	2010-06-10T00:00:00.000Z	Amazonian Emergency Fund	Percy	Fawcett
accounts	3	3	2010-10-09T00:00:00.000Z	Amazonian Emergency Fund
employees	1	1	2010-10-10T00:00:00.000Z	DK Banana Account	Donkey	Kong	city 1
accounts	2	2	2010-10-10T00:00:00.000Z	Kurzweil's invetions moneyz baby!			city 2

---

View on DB Fiddle

Answer 2

If your output is just table name and timestamp, then you don't need any JOIN. Just UNION employees and accounts.

select tablename, timestamp from
(select 'accounts' tablename, timestamp from accounts
union
select 'employees' tablename, timestamp from employees) a
order by timestamp

Otherwise, since the tables don't have same columns names, you'll need to make them having same names using column aliases.

select tablename, name, timestamp from
(select 'accounts' tablename, account_name 'name', timestamp from accounts
union
select 'employees' tablename, concat(first_name,last_name) 'name',  timestamp from employees
) a
order by timestamp

Answer 3

I am unsure what you try to achieve. but you have to "pad" the number of columns missing, but the second query doesn't have any rpws so you don't see it in you fiddle

select *, e.timestamp, sn.full_name from employees e
    INNER JOIN short_name as sn on sn.account_id = e.id    
Union all
select null,null,null,null,null,NULL,NULL, a.timestamp,'' 
from accounts a
where timestamp >= '2022-03-25T13:00:00'
and timestamp < '2022-04-04T13:00:00'
AND timestamp IS NOT NULL
order by 8;

Answer 4

SELECT 'employee' as type, e.id, e.timestamp, sn.full_name 
FROM employees e
INNER JOIN 
short_name as sn on sn.account_id = e.id
UNION ALL
SELECT 'account' as type, a.id, a.timestamp, '' as short_name 
FROM 
accounts a
WHERE timestamp IS NOT NULL
ORDER BY timestamp;

You can use dummy-columns to make the column-count fitting. To show the concept, I dropped the timestamp-filter. You can of course re-add it and also select additional columns (but of course then you also have to use some more dummy-columns)