1

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?

  • actually if i create a view between t1 and t2, the result table view returned only 150 records... did it maybe leave records that only matches? hmmm... I want to keep all records though – Pil Kwon Dec 21 '17 at 16:19
6

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;
  • OMG... you are my here – Pil Kwon Dec 21 '17 at 16:37
  • hmm.. i dont know why though it's missing 2 records somehow. i will figure that out and leave a comment. However, if you would know why, please also let me know – Pil Kwon Dec 21 '17 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.