So I have
- a table that stores asymmetrical connections between two persons (like a Twitter follow; not like a Facebook friend) and
- a table that stores a person's affiliations to various groups
My task is to find, for each asymmetrical relationship, the number of affiliations shared between the "from person" and the "to person".
I made this brute force solution, but I'm wondering if brighter minds could come up with something more efficient.
select frm01.from_person_id, frm01.to_person_id, count(*) num_affl
from
(
select lnk.from_person_id, lnk.to_person_id, ga.grp_id from_grp_id
from links lnk
left outer join grp_affl ga on lnk.from_person_id = ga.person_id
group by lnk.from_person_id, lnk.to_person_id, grp_id
) frm01
inner join
(
select lnk.from_person_id, lnk.to_person_id, ga.grp_id to_grp_id
from links lnk
left outer join grp_affl ga on lnk.to_person_id = ga.person_id
group by lnk.from_person_id, lnk.to_person_id, grp_id
) to01
on (
frm01.from_person_id = to01.from_person_id
and frm01.to_person_id = to01.to_person_id
and frm01.from_grp_id = to01.to_grp_id
)
group by frm01.from_person_id, frm01.to_person_id;
Using ANSI SQL on Netezza (which doesn't allow correlated subqueries).
TIA!
Edited to add table schema:
table lnk:
from_person_id to_person_id
1 4
2 5
3 6
4 2
5 3
table grp_affl:
person_id grp_id
1 A
1 B
1 C
2 A
3 B
4 C
5 A
5 B
5 C
6 A
expected output:
from_person_id to_person_id num_affl
1 4 1
2 5 1
3 6 0
4 2 0
5 3 1
Persons 1 & 4 have 1 affiliation in common (C), 2 & 5 have A in common, 5 & 3 have B in common. 3 & 6 have nothing in common. Likewise 4 & 2.
You can do this with aggregation and the right joins:
The joins bring in the groups. The last condition only brings in matching groups between the two persons. The final group by counts them.