hi,
try
select
r.*
from
rivers r
join jointable j1 on r.r_id=j1.mn_2_r_id join communities c1 on
j1.mn_2_c_id=c1.c_id and c1.C_Name='community_1'
join jointable j2 on r.r_id=j2.mn_2_r_id join communities c2 on
j2.mn_2_c_id=c2.c_id and c2.C_Name='community_2'
join jointable j3 on r.r_id=j3.mn_2_r_id join communities c3 on
j3.mn_2_c_id=c3.c_id and c3.C_Name='community_3'
join jointable j4 on r.r_id=j4.mn_2_r_id join communities c4 on
j4.mn_2_c_id=c4.c_id and c4.C_Name='community_4'
where
r.R_Name='river_1'
/tm
Ludwig Kniprath schrieb:
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
begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:t.markus@xxxxxxxxxxxxx
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
