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