Re: Query m:n-Combination

In article <4901993F(dot)9000401(at)kni-online(dot)de>,
Ludwig Kniprath <ludwig(at)kni-online(dot)de> writes:

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

Probably the fastest way is to do an OR join and counting the matches:

SELECT r.r_name
FROM rivers r
JOIN join_table j ON j.mn2_r_id = r.r_id
JOIN communities c ON c.c_id = j.mn2_c_id
WHERE c.c_name IN ('community_1', 'community_2',
'community_3', 'community_4')
GROUP BY r.r_name
HAVING count(*) = 4