What's the best way to join multiple unrelated tables in PostgreSQL?

autodan 0
Viewed 43

I have multiple unrelated product tables in PostgreSQL that I would like to combine in one extraction. All of them have dates and timestamps, statuses with different values per product, and I would like to get the count of each statuses from each product by hour.

Product 1

Timestamp Status Count
2022-09-11 01:35:38 Success 28
2022-09-11 17:35:02 Failed 54

Product 2

Timestamp Status Count
2022-09-11 03:45:27 For Approval 12
2022-09-11 12:35:54 Approved 82

Product 3

Timestamp Status Count
2022-09-11 09:45:23 Cancelled 3
2022-09-11 22:02:43 For Cancellation 22

Desired Output

Product Timestamp Status Count
Product 1 2022-09-11 01:00 Success 28
Product 1 2022-09-11 17:00 Failed 54
Product 2 2022-09-11 03:45 For Approval 12
Product 2 2022-09-11 12:35 Approved 82
Product 3 2022-09-11 09:45 Cancelled 3
Product 3 2022-09-11 02:02 For Cancellation 22
sql postgresql
1 Answers

you can use union or union all :

select 'Product 1' as product, Timestamp, Status, Count
from Product1
union all
select 'Product 2' as product, Timestamp, Status, Count
from Product2
union all
select 'Product 3' as product, Timestamp, Status, Count
from Product3
eshirvana0
Related