Re: Query m:n-Combination

On Fri, Oct 24, 2008 at 03:05:33PM +0200, Albe Laurenz wrote:
> Ludwig Kniprath wrote:
> > 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 r.r_name FROM rivers AS r
> JOIN join-table j1 ON (r.r_id = j1.mn_2_r_id)
> JOIN join-table j2 ON (r.r_id = j2.mn_2_r_id)
> JOIN join-table j3 ON (r.r_id = j3.mn_2_r_id)
> JOIN join-table j4 ON (r.r_id = j4.mn_2_r_id)
> WHERE j1.mn_2_c_id = (SELECT c_id FROM communities WHERE c_name = 'community_1')
> AND j2.mn_2_c_id = (SELECT c_id FROM communities WHERE c_name = 'community_2')
> AND j3.mn_2_c_id = (SELECT c_id FROM communities WHERE c_name = 'community_3')
> AND j4.mn_2_c_id = (SELECT c_id FROM communities WHERE c_name = 'community_4')

An alternative would be to move the work into the aggregation stage:

SELECT r.r_id
FROM rivers r, communities c, "join-table" j
WHERE r.r_id = j.mn_2_r_id
AND c.c_id = j.mn_2_c_id
GROUP BY r.r_id
HAVING bool_or(c.name = 'community_1')
AND bool_or(c.name = 'community_2')
AND bool_or(c.name = 'community_3')
AND bool_or(c.name = 'community_4')
AND bool_or(c.name = 'community_5');

You may need to put a "c.name IN ('community_1', 'community_2'"...
expression into the WHERE clause to give the planner some traction to
optimize things, but it's not needed for correctness.

Sam