From: | Swärd Mårten <Marten(dot)Sward(at)sweco(dot)se> |
---|---|
To: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Aggregate and join problem |
Date: | 2012-03-01 10:30:27 |
Message-ID: | 6154D5A32856B44299D1383E5C97259F0308D5@essth103.sweco.se |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi folks
I have some troubles to create a SQL-query and my hope is that someone of you could help me with this..
It's somewhat difficult to explain what I want to do but I'll give it a try and see if you can understand the problem.. Ahh fuck this.. It's almost imposible to explain.. :) I don't understand what it mys self after I have written it.. Read and see if you understand.. :)
I have three tables:
Table_1:
A table with meta data for areas (names, geometries and so..). Every area has a unique id.
Example:
101 | 'small area' | 'area name' | geom
102 | 'small area' | 'area name' | geom.
103 | 'small area' | 'area name' | geom.
104 | 'LARGE area' | 'area name' | geom
Table 2.
A table with values for some smaller areas. Contains a reference to an id in table1 and a value
Example:
101 | 12.5
102 | 5.5
103 | 6.5
Table_3:
A cross reference table with id:s for witch areas are connected to each other. Eg. What smaller areas that's is inside a larger area.
A larger area could have many smaller areas connected to it
Contains a reference to table 1 for the smaller areas and a reference to table 1 for the larger area.
Example:
101 | 104
102 | 104
103 | 104
What I want to do is the following:
The larger area should "inherit" the lowest value from the smaller areas that are connected to it.
I want to be able to get all larger areas and let them have a value that is the lowest value from table 2.
If you look at the example data I only want to get the larger area (104) from table 1 with a value from table 2 that is the lowest value of the areas connected to id 104. The result would be: 104 | 'LARGE area' | 'area name' | geom. | (value from table 2 id 102)
Best regards, Mårten
From | Date | Subject | |
---|---|---|---|
Next Message | Oliveiros d'Azevedo Cristina | 2012-03-01 10:49:14 | Re: Aggregate and join problem |
Previous Message | Philip Couling | 2012-03-01 09:30:21 | Re: No sort with except |