How would I write this as an SQL?

[GuitarGod]

Hello all,

 

I'm currently dabbling in some social networking software, and all is going well so far, expect I'm having trouble with one SQL. I posted this in the PHP forum instead of the SQL because it's more about how to write the SQL.

 

I am not good at explaining problems, so forgive me if I tend to ramble on

 

I have a friends list table, in which two users can become "friends". The fields are simply friend1 and friend2 - both filled with the user IDs of users who are friends. E.G. if user 204 and user 326 become friends, then a row in the friends table will be inserted accordingly. User 204 could have many rows in the friends table if they have many friends.

 

So, if user 204 is viewing user 326's profile I would like an SQL to pull all "mutual" friends from. Let's say user 440 is friends with both 204 and 326, how do I create an SQL to pull this off and find all other mutual friends too?

 

If I didn't explain that good, let me know and I'll try to reword it.

 

Thanks for all help!

[lemmin]

I started typing this up thinking it would be simple, but then I realized why my original idea wouldn't work. Here is what I ended up with:

SELECT
   userid
FROM
   users
WHERE
   (userid IN
      (SELECT
         friend2
      FROM
         relationships
      WHERE
         friend1 = $currentUser) OR
   userid IN
      (SELECT
         friend1
      FROM
         relationships
      WHERE
         friend2 = $currentUser)) AND
   (userid IN
      (SELECT
         friend2
      FROM
         relationships
      WHERE
         friend1 = $currentFriend) OR
   userid IN
      (SELECT
         friend1
      FROM
         relationships
      WHERE
         friend2 = $currentFriend)) AND
   userid != $currentUser AND
   userid != $currentFriend

 

There might be a better way to do this, but I think this should work.

[kickstart]

Hi

 

Assuming that each person will have 2 rows for a friendship (ie, there is a row with friend1  204 and friend2 of 326, plus another with friend1 of 326 and friend2 of 204, so you can have a friend one way and not the other), then this should do it:-

 

SELECT friend2
FROM (SELECT friend2
FROM friendsList
WHERE friend1 = 204) a
INNER JOIN
(SELECT friend2
FROM friendsList
WHERE friend1 = 326) b
ON a.friend2 = b.friend2

 

If there is just one row, with the order of friend1 / friend2 being pretty random then:-

 

SELECT friend2
FROM (SELECT friend2 AS Friend
FROM friendsList
WHERE friend1 = 204
UNION
SELECT friend1 AS Friend
FROM friendsList
WHERE friend2 = 204) a
INNER JOIN
(SELECT friend2 AS Friend
FROM friendsList
WHERE friend1 = 326
UNION
SELECT friend1 AS Friend
FROM friendsList
WHERE friend2 = 326) b
ON a.Friend = b.Friend

 

All the best

 

Keith

[GuitarGod]

No, what you wrote is absolutely perfect. It's works a treat Thank you very much!! Really, thank you