MYSQL选择共同的朋友 [英] MYSQL select mutual friends

问题描述

我的朋友"表具有以下列:id，userID，userID2，状态

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

userID和userID2没有特定的放入数据库顺序.

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

如果friendID = $ session(我已将其添加到第二个查询中)，它也不应返回该行

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

编辑: 我想返回$ id和$ session共有的FriendID行.我不确定为什么它不起作用.

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.

推荐答案

解释之后(并实际阅读了相互"部分):

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 

这篇关于MYSQL选择共同的朋友的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！