Re: Query m:n-Combination

Ludwig Kniprath napisal 24.10.2008 11:45:

>
> 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?

select mn_2_r_id from join_table
where mn_2_c_id in (1,2,3,4)
group by mn_2_r_id having count(*)=4

(4 = how many communities we should find)

--
Regards,
Tomasz Myrta