mySQL "friends in common" query

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

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".