select statement + join n' stuff

本文关键字：stuff statement join select | 更新日期: 2023-09-27 18:13:30

SELECT *
FROM LiveStreams l 
INNER JOIN Friendships f ON f.Sender = l.Sender 
                         OR f.Sender = l.Recipient 
                         OR f.Recipient = l.Sender 
                         OR f.Recipient = l.Recipient 
WHERE l.Sender = @SomeUser OR l.Recipient = @SomeUser

我现在的尝试^

所以，我在这里要做的是从(livestreams表)中获得给定@SomeUser的所有朋友的所有流。我们可以通过下面的查询来了解好友:

SELECT 
  Id, 
  CASE
    WHEN Sender = @UserName THEN Recipient 
    ELSE Sender 
  END AS Friend, 
  IsPending, 
  DateTime 
FROM Friendships 
WHERE (Sender = @UserName 
OR Recipient = @UserName) 
AND IsPending = @IsPending;

给定一个@SomeUser，我想从有3列(Id，发件人，收件人)的友谊表中查找他/她的朋友，使用单行(其中朋友可以是发件人或收件人)，我想获得所有的朋友，然后从livestream表(Id，值，发件人，收件人)获得他们指定的流

有点像Facebook的NewsFeed，当一个@Person进入直播页面时，我们将只查找来自他的朋友的所有流。如果他的朋友发起了这个流或者被发送了这个流，那么他们也可以出现在任何一个列上。发件人或收件人。

如何做到这一点?泰

我该怎么做呢?

* *

更新:

* *

谢谢你的回答。

SELECT l.* LiveStreams l 
INNER JOIN Friendships f ON f.Sender = 'beta' OR f.Recipient = 'beta'
WHERE l.Sender = 'beta' OR l.Recipient = 'beta' OR 
   l.Sender = f.Recipient OR l.Sender = f.Sender OR 
   l.Recipient = f.Sender OR l.Recipient = f.Recipient

不起作用的是，它仍然显示feed，其中beta是流发送者或接收者，这是我不想要的，因为他不是自己的朋友。有解决办法吗?再次感谢。

我不知道这是否有帮助，但我就是这样得到某人的朋友名单的。

    "SELECT foo.* " +
    "FROM (SELECT Id, Sender, CASE " +
    "WHEN Sender = @UserName THEN Recipient " +
    "ELSE Sender " +
    "END AS UserName, IsPending, DateTime " +
    "FROM Friendships WHERE " +
    "Sender = @UserName " +
    "or Recipient = @UserName " +
    ") as foo " +
    "where foo.UserName = @FriendName;";

select statement + join n' stuff

这应该可以完成工作:

SELECT l.* FROM LiveStreams l
INNER JOIN FriendShips f ON f.Sender = @SomeUser OR f.Recipient = @SomeUser
WHERE l.Sender = @SomeUser OR l.Recipient = @SomeUser OR
   l.Sender = f.Recipient OR l.Sender = f.Sender OR
   l.recipient = f.sender OR l.recipient = f.recipient

如果用户只能看到好友流:

SELECT l.* FROM LiveStreams l
INNER JOIN FriendShips f ON f.Sender = @SomeUser OR f.Recipient = @SomeUser
WHERE l.Sender <> @SomeUser AND l.Recipient <> @SomeUser AND (
   l.Sender = f.Recipient OR l.Sender = f.Sender OR
   l.recipient = f.sender OR l.recipient = f.recipient
)

SELECT l.* 
FROM LiveStreams l  
INNER JOIN (
  SELECT  
    f2.Id,  
    CASE WHEN f2.Sender = @UserName 
         THEN f2.Recipient  
         ELSE f2.Sender END AS Friend,  
    f2.IsPending,  
    f2.DateTime  
  FROM Friendships f2 
  WHERE (@UserName IN (f2.Sender, f2.Recipient))  
  AND f2.IsPending = @IsPending) f ON 
                         (f.Sender    IN (l.Sender, l.Recipient)
                       OR f.Recipient IN (l.Sender, l.Recipient))  
WHERE @SomeUser IN (l.Sender, l.Recipient)
相关文章：