Query m:n-Combination

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:

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