Query m:n-Combination

From: Ludwig Kniprath <ludwig(at)kni-online(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Query m:n-Combination
Date: 2008-10-24 09:45:35
Message-ID: 4901993F.9000401@kni-online.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear list,
I have to solve a simple Problem, explained below with some sample-Data.

A typical M:N-constellation, rivers in one table, communities in the
other table, m:n-join-informations (which river is running in which
community) in a third table.

Table rivers:
R_ID R_Name
1 river_1
2 river_2
3 river_3
4 river_4
5 river_5

Table communities :
C_ID C_Name
1 community_1
2 community_2
3 community_3
4 community_4
5 community_5

Join-table
mn_2_r_id mn_2_c_id
1 1
1 2
1 3
1 4
2 1
3 2
3 5
4 3
...

(in real database this relation is an gis-relation with thousands of
rivers and countries, related by spatial join, but the problem is the
same...)

I want to know, which river is running through communities 1,2,3 *and* 4?
You can see the solution by just looking at the data above (only
"river_1" is running through all these countries), but how to query this
by sql?

Thanks in advance
Ludwig

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stefan Sturm 2008-10-24 11:07:48 Re: Need Tool to sync databases with 8.3.1
Previous Message Sergey Levchenko 2008-10-24 09:18:17 partitioning question. need current month and archive partitions.