CODEWITHSUNDEEP
sqlpostgresql
autodan
autodan

Reputation: 15

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

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

Upvotes: 0

Views: 116

Answers (1)

eshirvana
eshirvana

Reputation: 24568

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

Upvotes: 2

Related Questions