Thread: Query m:n-Combination
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? Thanks in advance Ludwig
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
hi,
try
select
r.*
from
rivers r
join jointable j1 on r.r_id=j1.mn_2_r_id join communities c1 on
j1.mn_2_c_id=c1.c_id and c1.C_Name='community_1'
join jointable j2 on r.r_id=j2.mn_2_r_id join communities c2 on
j2.mn_2_c_id=c2.c_id and c2.C_Name='community_2'
join jointable j3 on r.r_id=j3.mn_2_r_id join communities c3 on
j3.mn_2_c_id=c3.c_id and c3.C_Name='community_3'
join jointable j4 on r.r_id=j4.mn_2_r_id join communities c4 on
j4.mn_2_c_id=c4.c_id and c4.C_Name='community_4'
where
r.R_Name='river_1'
/tm
Ludwig Kniprath schrieb:
> 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?
>
> Thanks in advance
> Ludwig
>
Attachment
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
In article <4901993F.9000401@kni-online.de>,
Ludwig Kniprath <ludwig@kni-online.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
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