pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAggregate and join problem
4+ messages / 2 participants
[nested] [flat]
* Aggregate and join problem
@ 2012-03-01 10:30 Swärd Mårten <Marten.Sward@sweco.se>
0 siblings, 1 reply; 4+ messages in thread
From: Swärd Mårten @ 2012-03-01 10:30 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Aggregate and join problem
@ 2012-03-01 10:49 Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
parent: Swärd Mårten <Marten.Sward@sweco.se>
0 siblings, 1 reply; 4+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2012-03-01 10:49 UTC (permalink / raw)
To: Swärd Mårten <Marten.Sward@sweco.se>; pgsql-sql
Hi, Swärd,
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
JOIN Table_2 t2
ON t3.col1 = t2.col1
GROUP BY t3.col2
) query1
JOIN Table_1 t1
ON t1.col1 = query1.t1_id
----- Original Message -----
From: Swärd Mårten
To: pgsql-sql@postgresql.org
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
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Aggregate and join problem
@ 2012-03-01 11:01 Swärd Mårten <Marten.Sward@sweco.se>
parent: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 1 reply; 4+ messages in thread
From: Swärd Mårten @ 2012-03-01 11:01 UTC (permalink / raw)
To: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>; pgsql-sql
It worked like a charm! Many thanks for that great solution!
Best regards, Mårten
Från: Oliveiros d'Azevedo Cristina [mailto:oliveiros.cristina@marktest.pt]
Skickat: den 1 mars 2012 11:49
Till: Swärd Mårten; pgsql-sql@postgresql.org
Ämne: Re: [SQL] Aggregate and join problem
Hi, Swärd,
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
JOIN Table_2 t2
ON t3.col1 = t2.col1
GROUP BY t3.col2
) query1
JOIN Table_1 t1
ON t1.col1 = query1.t1_id
----- Original Message -----
From: Swärd Mårten<mailto:Marten.Sward@sweco.se>
To: pgsql-sql@postgresql.org<mailto:pgsql-sql@postgresql.org>
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.. :) 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Aggregate and join problem
@ 2012-03-01 11:04 Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
parent: Swärd Mårten <Marten.Sward@sweco.se>
0 siblings, 0 replies; 4+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2012-03-01 11:04 UTC (permalink / raw)
To: Swärd Mårten <Marten.Sward@sweco.se>; pgsql-sql
Great to Hear!
Best,
Oliver
----- Original Message -----
From: Swärd Mårten
To: Oliveiros d'Azevedo Cristina ; pgsql-sql@postgresql.org
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årten
Från: Oliveiros d'Azevedo Cristina [mailto:oliveiros.cristina@marktest.pt]
Skickat: den 1 mars 2012 11:49
Till: Swärd Mårten; pgsql-sql@postgresql.org
Ämne: Re: [SQL] Aggregate and join problem
Hi, Swärd,
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
JOIN Table_2 t2
ON t3.col1 = t2.col1
GROUP BY t3.col2
) query1
JOIN Table_1 t1
ON t1.col1 = query1.t1_id
----- Original Message -----
From: Swärd Mårten
To: pgsql-sql@postgresql.org
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
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-03-01 11:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-01 10:30 Aggregate and join problem Swärd Mårten <Marten.Sward@sweco.se>
2012-03-01 10:49 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2012-03-01 11:01 ` Swärd Mårten <Marten.Sward@sweco.se>
2012-03-01 11:04 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox