mySQL "friends in common" query

Question

Im trying to get a set of "Friends-in-Common" via the friends table:

And then I would like to select * from the users table WHERE userId is in the friends in common set.

friends
.id...dom...sub
.1.....2.....1
.2.....1.....3
.3.....1.....4
.4.....1.....5
.5.....2.....4
.6.....2.....6
.7.....3.....2
.8.....3.....6
.9.....2.....3

users
.id....fname...lname
.1.....ann.....fox
.2.....rob.....smith
.3.....amy.....oconnor
.4.....mark....brown
.5.....jack....hughes
.6.....sian....jong

From the above tables you can see that ann(user_1) is friends with 2,3,4 and 5... And rob(user_2) is friends with 1, 3, 4 and 6.

But how do I write a query to get the userIds from the usersTable, of the friends in common, between two given users??

I have a feeling it has something to do with UNION, but any sugestions are welcome...

Thanks guys

Answer 1

Here's what I come up with:

SELECT distinct fic.* from users l
JOIN friends fol ON (fol.dom=l.id OR fol.sub=l.id)
JOIN users fic ON (fol.dom=fic.id OR fol.sub=fic.id)
JOIN friends fofic ON (fofic.dom=fic.id OR fofic.sub=fic.id)
JOIN users r ON (r.id=fofic.dom OR r.id=fofic.sub)
WHERE r.id=1 AND l.id=2 AND fic.id NOT IN (1,2);

I was aliasing tables as fic = friends in common, fofic = friends of friends in common. I started with the 'left' user, find his friends, find frend-of-friends that matched the 'right' user.

The final "not in" is to keep us from going "amy is a friend of _ who is a friend of amy".