ACCES count common friends / nodes (compare within 1 table)

[LittleBoeddha]

I have the table1 below and want to make a table that shows how many friends Ent1 has in common with any other person from Ent1. Eg A (short for AAA) has friends B,C,D,E,F while B has friends A,C,E,F. They have C,E,F in common so the outcome should be AAA BBB 3
Can this be done in acces/SQL? I have no clue ...
Table1 Ent1 Ent2 link
AAA BBB friend
AAA CCC friend
AAA DDD friend
AAA EEE friend
AAA FFF friend
BBB AAA friend
BBB CCC friend
BBB EEE friend
BBB FFF friend
CCC AAA friend
CCC BBB friend
CCC EEE friend
CCC FFF friend
DDD AAA friend
DDD KKK friend
DDD LLL friend
EEE AAA friend
EEE BBB friend
EEE CCC friend
FFF AAA friend
FFF BBB friend
FFF CCC friend
KKK DDD friend
LLL DDD friend
The outcome should be :
AAA BBB 3
AAA CCC 3
AAA DDD 0
AAA EEE 2
AAA FFF 2
AAA KKK 1
AAA LLL 1
BBB CCC 3 etc...

 

[stumac]

If you join a table to an alias of itself then this should be possible... Not sure about having the Zero in if there is no matches - you would probably have to add in another copy of the table for that... If that isn't required then something like below:

SELECT A.Ent1, B.Ent1 AS OtherPerson, Count(B.Ent2) AS MutualFriends

FROM Table1 AS A  INNER JOIN Table1 AS B ON A.Ent2 = B.Ent2

WHERE A.Link="Friend" AND B.Link="Friend"

GROUP BY A.Ent1, B.Ent1

HAVING A.Ent1 Not Like B.Ent1

 

[LittleBoeddha]

If you join a table to an alias of itself then this should be possible... Not sure about having the Zero in if there is no matches - you would probably have to add in another copy of the table for that... If that isn't required then something like below:

SELECT A.Ent1, B.Ent1 AS OtherPerson, Count(B.Ent2) AS MutualFriends

FROM Table1 AS A  INNER JOIN Table1 AS B ON A.Ent2 = B.Ent2

WHERE A.Link="Friend" AND B.Link="Friend"

GROUP BY A.Ent1, B.Ent1

HAVING A.Ent1 Not Like B.Ent1

I think your suggestion is the better option, showing the zero results would lead to too many records anyway (n*(n+1))/2 records.

I'll put it to the test asap, many thanx!