Merging two views in PostgreSQL

Question

I have two views in below format.

ProductId Version IsAvailable
123       1       Yes
124       1       No
125       1       Yes
126       1       No

ProductId Version IsShippable
123       1       Yes
124       1       Yes
125       1       No
127       1       Yes

I need to merge these two tables into a single table:

ProductId Version IsAvailable IsShippable
123       1       Yes         Yes
124       1       No          Yes
125       1       Yes         No
126       1       No          Null
127       1       Null        Yes

How can I write the query to achieve this?

You should show us the original source data and the code for the two views. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 31, 2018 at 4:53
Because a view is the product of a query. Unless you used the term "view" to mean "table?" Please clarify whether you are showing us tables or views on top of tables. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 31, 2018 at 4:55
These are views but both the views and tables work in the same way. — sgowd
– sgowd, Commented Aug 31, 2018 at 4:56

Fahmi · Accepted Answer · 2018-08-31 04:51:43Z

1

Use full outer join between 2 views like below:

select a.ProductId, a.Version, IsAvailable, IsShippable 
from tableA a
full outer join tableB b on a.productid =b.productid and a.version=b.version

answered Aug 31, 2018 at 4:51

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sgowd Over a year ago

Doesn't it just join the common rows?

sgowd Over a year ago

But in this case, the product id is coming null for rows that are not present in tableA.

Collectives™ on Stack Overflow

Merging two views in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related