It worked like a charm! Many thanks for that great solution!=

Best regards, M=E5rten

Fr=E5n: Olivei= ros d'Azevedo Cristina [mailto:oliveiros.cristina@marktest.pt]
Skickat: den 1 mars 2012 11:49
Till: Sw=E4rd M=E5rten; pgsql-sql@postgresql.org
=C4mne: Re: [SQL] Aggregate and join problem

Hi, Sw=E4rd,

As you didn't name your tables' columns I= decided to call them col1, col2, etc.

I dunno if this will do what you want as = it is completely untested code.

But, give it a try and see if it works an= d if it doesn't, tell me the error, and we'll continue from there.

You'll have to substitute my col1, col2, = etc with your actual column names.

Best,

Oliveiros

SELECT query1.t1_id, t1.col2,t1.col3,t1.col4, query1.minimum

FROM (

SELECT t3.col2 as t1_id, MIN(t2.col2) as minimum

FROM Table_3 t3

JOIN Table_2 t2

ON t3.col1 =3D t2.col1

GROUP BY t3.col2

) query1

JOIN Table_1 t1

ON t1.col1 =3D query1.t1_id

----- Original Message -----

From: Sw= =E4rd M=E5rten

To: pgsql-sql@postgresql.org

Sent: Thursday, = March 01, 2012 10:30 AM

Subject: [SQL] A= ggregate and join problem

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 f= uck this.. It’s almost imposible to explain.. J I= don’t understand what it mys self after I have written it.. Read and see if you understand.. J

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’ | geo= m

102 | ‘small area’ | ‘area name’ | geo= m.

103 | ‘small area’ | ‘area name’ | geo= m.

104 | ‘LARGE area’ | ‘area name’ | geo= m

Table 2.

A table with values for some smaller areas. Contains a referen= ce 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 connecte= d to each other. Eg. What smaller areas that’s is inside a larger are= a.

A larger area could have many smaller areas connected to it

Contains a reference to table 1 for the smaller areas and a re= ference 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=E5rten