结合两个MYSQL查询，只获取朋友的帖子 - Combining two MYSQL queries to get posts by friends only

Combining two MYSQL queries to get posts by friends only

我有一个查询来获取用户的好友，还有一个查询可以获取用户作为状态发布的所有帖子，现在我需要将它们组合起来。

朋友：

SELECT CASE WHEN userID=$session THEN userID2 ELSE userID END AS friendID 
FROM friends 
WHERE userID=$session OR userID2=$session

然后用户发布：

SELECT * FROM posts 
WHERE toID=fromID AND state='0' 
ORDER BY id DESC LIMIT 10

所以他们应该一起工作。来自第一个查询的friendID应该等于ID或fromID，这并不重要，因为它们都需要相同。

你发布帖子的第二个条件是它们来自朋友。因此：

SELECT * FROM posts  
WHERE toID=fromID AND state='0' AND toID IN
    (SELECT CASE WHEN userID=$session THEN userID2 ELSE userID END AS friendID  
     FROM friends  
     WHERE userID=$session OR userID2=$session)
ORDER BY id DESC LIMIT 10

您应该使用JOIN:

SELECT 
    CASE 
        WHEN userID=$session THEN userID2 
        ELSE userID 
    END AS friendID, 
    posts.*
FROM friends 
JOIN posts on posts.toID = friends.friendID
WHERE (userID=$session OR userID2=$session)
AND toID=fromID 
AND state='0' 
ORDER BY id DESC LIMIT 10