检索我不遵循LeftJoin Sql的用户的帖子

Question

我有三个表:用户，社区和帖子。

我想检索乔不遵循(id_user=1)的用户的帖子

用户

    id_user  |  name
       1        Joe
       2        Doe
       3        Moe
       4        Roe
       5        Clin

社区

  id_follower | id_followed
       1            3
       1            5

发布

    id_post   | id_user | post  
      24            4       hi
      25            5       hello
      26            1       how are you
      27            3       come on
      28            4       let go
      29            2       get out

我想要找回的是

      24            4       hi - by Roe
      28            4       let go  - by Roe
      29            2       get out  - by Doe     

我试过了但不起作用

SELECT p*
FROM community as c
LEFT JOIN users as u ON u.id_user=c.id_followed
LEFT JOIN posts as p ON p.id_user!=c.id_followed 
WHERE c.id_follower=1 AND u.id_user!=1 

Answer 1

这应该是可行的：

select * from Posts 
where id_user not in
(select id_followed from Community where id_follower = 1)

Answer 2

只要给出一个答案就没有意义了，让我们跨过逻辑。还有其他方法可以这样做，但是既然你问了左联接，我就像左连接一样做。从岗位开始..。

from posts p

让我们离开加入社区

left join community c on c.id_followed = p.id_user and ID_follower = 1

这现在给出了所有帖子以及社区id_followed record...if的列表--社区的记录为空，然后是来自用户的-- joe不遵循。因为我们只想要记录乔不跟著

where c.id_followed is null

我们将向用户添加一个连接，以获取海报名称，并将其全部放在一起。

from posts p
left join community c on c.id_followed = p.id_user and ID_follower = 1
left join users u on u.id_user = p.id_user
where c.id_followed is null  

最后，将select行放在一起以获取您想要的字段，使用concat按名称格式获取post。

select p.id_post, p.id_user,  concat(p.post  , ' by ', u.name)

把它放在一起快跑！

Answer 3

您可以在not in上使用内部连接。

select p.*
from  Posts p 
inner join (
  select id_user form community 
  where id_user  not in (
   select id_followed
   from community 
   where id_follower =1
  )
) t  on p.id_user = t.id_user and p.id_user <>1