mysql结合3个表来获取用户和被关注的用户的帖子

Question

我有 3 表用户，流和关注

下表:

follow_id   user_id_follower   user_id_followed
    1              9                  2 
    2              9                  4 

用户表

id   username   
1     test1         
2     test2

流表

id user_id  post_content    post_image   
1     9     Hi there        uploads/1.jpg
2     9     Another         uploads/2.jpg

这给了用户帖子

SELECT users.username, stream.post_content, stream.post_image 
FROM users INNER JOIN stream ON users.id=stream.user_id 
WHERE stream.user_id = 9

这给用户关注帖子的人

SELECT users.username, stream.post_content, stream.post_image, 
follow.user_id_follower, follow.user_id_followed FROM follow
INNER JOIN stream ON follow.user_id_followed = stream.user_id
INNER JOIN users ON follow.user_id_followed = users.id
WHERE follow.user_id_follower = 9 // this change depending on user

我在制作一条同时获取用户和关注帖子的 SQL 语句时遇到问题

Answer 1

你需要为此使用UNION ALL。

SELECT s.*, follower.username, 1 order_priority
  FROM stream s
  JOIN user follower ON s.user_id = follower.userid
 WHERE follower.user_id = 9
UNION ALL
SELECT s.*, following.username, 2
  FROM stream s
  JOIN user following ON s.user_id = following.userid
  JOIN follow f ON following.user_id = f.user_id_followed
       AND f.user_id_follower = 9
ORDER BY order_priority;