MYSQL select mutual friends

Question

My 'friends' table has the following columns: id, userID, userID2, state

userID and userID2 don't have a specific order of being put into the database.

I currently use this query to find a users friends:

$quer = mysql_query("
SELECT CASE WHEN userID=$id THEN userID2 ELSE userID END AS friendID 
FROM friends WHERE userID=$id OR userID2=$id");

I have tried this however it doesn't work:

SELECT 
CASE WHEN userID=$id OR userID=$session THEN userID2 
ELSE userID END AS friendID  
FROM friends WHERE (userID=$session OR userID2=$session) 
AND (userID=$id OR userID2=$id) AND friendID!=$session

also it shouldn't return the row if friendID=$session (which i have added to my second query)

EDIT: I want to return as friendID rows that $id and $session have in common. I'm not exactly sure why it isn't working.

What are you trying to get as output? Why does your second query "not work"? — mathematical.coffee, Jan 18 '12 at 06:40
You are using `$session` for both userID and friendID. Should that be the case? And what's the actual value of `$session`? — Arjan, Jan 18 '12 at 06:42
I want to return as friendID rows that $id and $session have in common. I'm not exactly sure why it isn't working. — Dylan Cross, Jan 18 '12 at 06:44
$session is the unique id of the user that is logged in, $id is the unique id of the user you're comparing with. I believe that should be the case, because I want them to find users in common — Dylan Cross, Jan 18 '12 at 06:46
The first query looks fine. Perhaps you should replace (in the 1st query) the `$id` with `$session` ? — ypercubeᵀᴹ, Jan 18 '12 at 06:52

score 5 · Accepted Answer · edited Dec 27 '13 at 23:50

5

After the explanations (and actually reading the "mutual" part):

SELECT a.friendID
FROM
      ( SELECT CASE WHEN userID = $id
                      THEN userID2 
                      ELSE userID 
               END AS friendID 
        FROM friends 
        WHERE userID = $id OR userID2 = $id
      ) a
  JOIN
      ( SELECT CASE WHEN userID = $session
                      THEN userID2 
                      ELSE userID 
               END AS friendID 
        FROM friends 
        WHERE userID = $session OR userID2 = $session
      ) b
    ON b.friendID = a.friendID

edited Dec 27 '13 at 23:50

Ben Swinburne

25,669
10
69
108

answered Jan 18 '12 at 06:49

ypercubeᵀᴹ

113,259
19
174
235

The first query was just an example of how I select the users friends (that works fine) However now I'm trying to select the users friends that another user has in common with them. – Dylan Cross Jan 18 '12 at 06:56
$id would be the opposing user, $session would be myself, So I want to find out what friends I have in common with $id – Dylan Cross Jan 18 '12 at 06:57
Ah, sorry. So, the two friends have ids: `$id` and `$session`. Ok, editing. – ypercubeᵀᴹ Jan 18 '12 at 06:57
Yes, and also it shouldn't return the row that the friendID would be equal to $session. (so it doesn't show yourself as a mutual friend) – Dylan Cross Jan 18 '12 at 06:59
If you do not have yourself as one of your friends, the query does not return your id. – Arjan Jan 18 '12 at 07:09
Perfect, thanks. I had just come up with a query that worked, but it was much messier. – Dylan Cross Jan 18 '12 at 07:10

MYSQL select mutual friends

1 Answers1

Linked

Related