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