Re: Query m:n-Combination

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
>