n-m (indirect) self-join
I'm struggling with the following problem:
Table T (ID; name): (1; "John"), (2; "Mary"), (3; "Peter"); (4; "Jane")
I have a second table that indicates who knows who, e.g. Mary knows John and Peter.
The relation is symmetrical, so if Mary knows John, John will know Mary.
On a form layout for table T that shows Mary, I need a button that stays within the same layout,
but finds all the people Mary knows (so John and Peter).
I tried a table R1 (ID1; ID2): (2; 1), (2; 3) with all person pairs that know each other,
and I tried a table R2 (id; ID): (a; 2), (a; 1), (b; 2), (b; 3) where id indicates the pair number.
But both solutions seem pretty clumsy and I can't really get it to work.
Any help is welcome. Thanks in advance.