Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 023271014388 for ; Thu, 1 Mar 2012 06:49:33 -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 1S33Zg-00061q-W1 for pgsql-sql@postgresql.org; Thu, 01 Mar 2012 10:49:32 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by hermes.marktest.pt (Postfix) with ESMTP id 66B3B1A835F; Thu, 1 Mar 2012 10:49:15 +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 R5GJU8XawwtI; Thu, 1 Mar 2012 10:49:15 +0000 (WET) Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by hermes.marktest.pt (Postfix) with ESMTP id 357D91A835E; Thu, 1 Mar 2012 10:49:15 +0000 (WET) Message-ID: From: "Oliveiros d'Azevedo Cristina" To: =?iso-8859-1?Q?Sw=E4rd_M=E5rten?= , References: <6154D5A32856B44299D1383E5C97259F0308D5@essth103.sweco.se> Subject: Re: Aggregate and join problem Date: Thu, 1 Mar 2012 10:49:14 -0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0088_01CCF798.F1C15250" 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: -0.0 (/) X-Archive-Number: 201203/7 X-Sequence-Number: 36402 This is a multi-part message in MIME format. ------=_NextPart_000_0088_01CCF798.F1C15250 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 =20 I have three tables: =20 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 =20 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 =20 =20 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 =20 =20 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. =20 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) =20 Best regards, M=E5rten ------=_NextPart_000_0088_01CCF798.F1C15250 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi, Sw=E4rd,
 
As you didn't name your tables' columns = I decided=20 to call them col1, col2, etc.
 
I dunno if this will do what you want = as it is=20 completely untested code.
 
But, give it a try and see if it works = and if it=20 doesn't, tell me the error, and we'll continue from there.
You'll have to substitute my col1, = col2, etc with=20 your actual column names.
 
Best,
Oliveiros
 
 
SELECT query1.t1_id, = t1.col2,t1.col3,t1.col4,=20 query1.minimum
FROM (
SELECT t3.col2 as t1_id, = MIN(t2.col2)=20 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=20 query1.t1_id
 
----- Original Message -----
From:=20 Sw=E4rd=20 M=E5rten
Sent: Thursday, March 01, 2012 = 10:30=20 AM
Subject: [SQL] Aggregate and = join=20 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 see=20 if you can understand the problem.. Ahh fuck this.. It=92s almost = imposible to=20 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 a=20 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 are=20 connected to it.

 

I=20 want to be able to get all larger areas and let them have a value that = is the=20 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 =  areas=20 connected to id 104. The result would be: 104 | =91LARGE area=92 | = =91area name=92 |=20 geom. | (value from table 2 id 102)

 

Best=20 regards,=20 M=E5rten

------=_NextPart_000_0088_01CCF798.F1C15250--