Postgresql: view from multiple tables (with same fields)

Question

I have following database schema:

I want to create view for all property types (flat,plot,house) with address fields, property price/size and user ID.

I have an sql script like this:

create or replace VIEW auction_view3 AS
    select ROW_NUMBER() OVER(ORDER BY f.userid) AS id, f.userid, r."type", a.street, a.homenumber, a.localnumber, a.city, a.postcode, f.price, f."size", f.rooms, f.floor 
        FROM address a, flat f, realassets r
        WHERE f.addressid = a.id AND a.realassetid =r.id

This script create view only for flats, it looks good, but also I want to show houses and plots in my view, I don't know how to put in my price column in view - price values from all property tables (plot, house, flat). How can I display data from three property tables in one view. I can achieve it through multiple selects?

Andronicus · Accepted Answer · 2020-04-22 07:49:14Z

4

The you need to use union, just like in plain select query:

create or replace view auction_view3 as
    select row_number() over(order by f.userid) as id, f.userid, r."type", a.street, a.homenumber, a.localnumber, a.city, a.postcode, f.price, f."size", f.rooms, f.floor 
    from address a, flat f, realassets r
    where f.addressid = a.id and a.realassetid =r.id 
    union all
    select row_number() over(order by p.userid) as id, p.userid, r."type", a.street, a.homenumber, a.localnumber, a.city, a.postcode, p.price, p."size", p.rooms, p.floor 
    from address a, plot p, realassets r
    where p.addressid = a.id and a.realassetid =r.id 
    union all
    select row_number() over(order by h.userid) as id, h.userid, r."type", a.street, a.homenumber, a.localnumber, a.city, a.postcode, h.price, h."size", h.rooms, h.floor 
    from address a, house h, realassets r
    where h.addressid = a.id and a.realassetid =r.id

Here union all is fast but does not remove duplicates, if you don't want, them - use union instead.

edited Apr 22, 2020 at 7:49

answered Apr 22, 2020 at 7:37

Andronicus

26.2k18 gold badges55 silver badges92 bronze badges

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

2 Comments

Andronicus Over a year ago

@GordonLinoff I don't know, maybe cross join is what OP needs, that's the query he has provided. His problem was to do the same for other tables and I have provided the answer.

Gordon Linoff Over a year ago

. . Your query is technically correct. This is directed to the upvotes on it -- people are free to upvote. I'm free to comment. Note that I didn't downvote the answer, only questioned the upvotes. The where clause is clearly implementing joins.

Collectives™ on Stack Overflow

Postgresql: view from multiple tables (with same fields)

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