Ludwig Kniprath wrote: > 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 > ... > > 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') (untested) Is that what you are looking for? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
