creating a view of multiple tables using Postgresql

Question

This might be the stupidest question for postgresql user. Please do understand that i just started to use postgresql.

what i am trying to do is joining 3 tables using same key field (which is polyong_id)

keyfield of table 1 = polygon_id, and it has additional fields that i want to keep keyfield of table 2 = polygon_id, and it has venue_id field that i want to append keyfield of table 3 = polyong_id, and it has feature_type field that i want to append

so what i did is

Create View BULD as
Select t1.shape, t1.polygon_id, t1.name, t1.height, t1.ground_clearance,
       t1.iso_country_code, t2.venue_id, t3.feature_type, t3.main_feature_type
From
       BULD_base as t1, Venue as t2, Feature as t3
Where
       t1.polygon_id = t2.polygon_id and t1.polygon_id = t3.polygon_id

BULD's total number of records is about 60K and the result returned around 150 records. I changed the AND to OR and it gave me same result.

I may create a view between t1 and t2, then and create another view between the table from the result above and t3 for the final result.

but I'd like to make a neat and smart query for this.

can anyone help?

Answer 1

The way the query is written, it will return entries whose polygon_id exist in the 3 tables. If you want the entries that exist in the main table and eventually add the values from the 2nd and 3rd tables, you need to use a left join.

Create View BULD as
Select t1.shape, t1.polygon_id, t1.name, t1.height, t1.ground_clearance,
       t1.iso_country_code, t2.venue_id, t3.feature_type, t3.main_feature_type
From
    BULD_base as t1
    LEFT JOIN Venue as t2 ON t1.polygon_id = t2.polygon_id 
    LEFT JOIN Feature as t3 ON t1.polygon_id = t3.polygon_id;