Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 29157101438A for ; Thu, 1 Mar 2012 07:04:45 -0400 (AST) Received: from iota.marktest.pt ([80.251.174.118] helo=hermes.marktest.pt) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S33oP-0006HQ-8U for pgsql-sql@postgresql.org; Thu, 01 Mar 2012 11:04:44 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by hermes.marktest.pt (Postfix) with ESMTP id A54A41A836E; Thu, 1 Mar 2012 11:04:28 +0000 (WET) X-Virus-Scanned: amavisd-new at marktest.pt Received: from hermes.marktest.pt ([127.0.0.1]) by localhost (hermes.marktest.pt [127.0.0.1]) (amavisd-new, port 11124) with ESMTP id rMqchslPCBed; Thu, 1 Mar 2012 11:04:28 +0000 (WET) Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by hermes.marktest.pt (Postfix) with ESMTP id 82E651A836D; Thu, 1 Mar 2012 11:04:28 +0000 (WET) Message-ID: <6642CFF102224D2492403CEC553EB3E3@marktestcr.marktest.pt> From: "Oliveiros d'Azevedo Cristina" To: =?iso-8859-1?Q?Sw=E4rd_M=E5rten?= , References: <6154D5A32856B44299D1383E5C97259F0308D5@essth103.sweco.se> <6154D5A32856B44299D1383E5C97259F030918@essth103.sweco.se> Subject: Re: Aggregate and join problem Date: Thu, 1 Mar 2012 11:04:28 -0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0097_01CCF79B.12209AE0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201203/9 X-Sequence-Number: 36404 This is a multi-part message in MIME format. ------=_NextPart_000_0097_01CCF79B.12209AE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Great to Hear! Best, Oliver ----- Original Message -----=20 From: Sw=E4rd M=E5rten=20 To: Oliveiros d'Azevedo Cristina ; pgsql-sql@postgresql.org=20 Sent: Thursday, March 01, 2012 11:01 AM Subject: Re: [SQL] Aggregate and join problem It worked like a charm! Many thanks for that great solution! Best regards, M=E5rten Fr=E5n: Oliveiros d'Azevedo Cristina = [mailto:oliveiros.cristina@marktest.pt]=20 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 and 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=20 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 -----=20 From: Sw=E4rd M=E5rten=20 To: pgsql-sql@postgresql.org=20 Sent: Thursday, March 01, 2012 10:30 AM Subject: [SQL] Aggregate 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 fuck 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=20 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=20 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.=20 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 ------=_NextPart_000_0097_01CCF79B.12209AE0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Great to Hear!
 
Best,
Oliver
----- Original Message -----
From:=20 Sw=E4rd=20 M=E5rten
To: Oliveiros d'Azevedo = Cristina=20 ; pgsql-sql@postgresql.org =
Sent: Thursday, March 01, 2012 = 11:01=20 AM
Subject: Re: [SQL] Aggregate = and join=20 problem

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

Best=20 regards, M=E5rten

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

Hi,=20 Sw=E4rd,

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

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

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

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

Best,

Oliveiros

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

FROM=20 (

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

FROM=20 Table_3 t3

JOIN=20 Table_2 t2

ON=20 t3.col1 =3D t2.col1

GROUP=20 BY t3.col2

)=20 query1

JOIN  Table_1=20 t1

ON=20 t1.col1 =3D query1.t1_id

----- = Original=20 Message -----

From: Sw=E4rd=20 M=E5rten

To: pgsql-sql@postgresql.org=20

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

Subject: [SQL] = Aggregate=20 and join problem

Hi=20 folks

I=20 have some troubles to create a SQL-query and my hope is that someone = of you=20 could help me with this..

It=92s=20 somewhat difficult to explain what I want to do but I=92ll give it a = try and=20 see if you can understand the problem.. Ahh fuck this.. It=92s = almost=20 imposible to explain.. J I=20 don=92t understand what it mys self after I have written it.. Read = and see if=20 you understand.. J=20

I=20 have three tables:

Table_1:

A=20 table with meta data for areas (names, geometries and so..). Every = area has=20 a unique id.

Example:

101=20 | =91small area=92 | =91area name=92 | geom

102=20 | =91small area=92 | =91area name=92 | geom.

103=20 | =91small area=92 | =91area name=92 | geom.

104=20 | =91LARGE area=92 | =91area name=92 | geom

Table=20 2.

A=20 table with values for some smaller areas. Contains a reference to an = id in=20 table1 and a value

Example:

101=20 | 12.5

102=20 | 5.5

103=20 | 6.5

Table_3:

A=20 cross reference table with id:s for witch areas are connected to = each other.=20 Eg. What smaller areas that=92s is inside a larger = area.

A=20 larger area could have many smaller areas connected to=20 it

Contains=20 a reference to table 1 for the smaller areas and a reference to = table 1 for=20 the larger area.

Example:

101=20 | 104

102=20 | 104

103=20 | 104

What=20 I want to do is the following:

The=20 larger area should =93inherit=94 the lowest value from the smaller = areas that=20 are connected to it.

I=20 want to be able to get all larger areas and let them have a value = that is=20 the lowest value from table 2.

If=20 you look at the example data I only want to get the larger area = (104) from=20 table 1 with a value from table 2 that is the lowest value of the=20  areas connected to id 104. The result would be: 104 | =91LARGE = area=92 |=20 =91area name=92 | geom. | (value from table 2 id = 102)

Best=20 regards,=20 M=E5rten

------=_NextPart_000_0097_01CCF79B.12209AE0--