Jump to content

How would I write this as an SQL?


Recommended Posts

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! :D

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.