当我使用WHERE IN条件时，SQL查询用户名不会出现

I want to display all the user post whom I am following and also my own posts.

我想显示我关注的所有用户帖子以及我自己的帖子。

But there is a problem with my query and the username result: When executed, I am getting the post correctly but the user details are wrong. It shows the same user details for all the posts. I suspect my WHERE ... IN clause is wrong.

但我的查询和用户名结果有问题:执行时,我正确地收到帖子,但用户详细信息错误。它显示了所有帖子的相同用户详细信息。我怀疑我的WHERE ... IN子句是错误的。

SELECT u.username,
       u.NAME,
       u.profile_pic,
       up.*
FROM   USER u,
       user_post up
WHERE  up.user_id IN ( (SELECT follow_id
                        FROM   follow
                        WHERE  follow_id = $user_id), $user_id )
       AND description = ''
ORDER  BY postime DESC

How can I get the user_post for the relevant user.

如何获取相关用户的user_post。

3 个解决方案

#1

You not mention any user_post in sub Query .. (SELECT follow_id FROM follow WHERE follow_id=$user_id)

你没有在子查询中提到任何user_post ..(SELECT follow_id FROM跟随WHERE follow_id = $ user_id)

So, use inner join in user table.. SELECT follow_id FROM follow inner join user_post up on follow_id=up.user_id

因此,在用户表中使用内部联接.. SELECT follow_id FROM跟随内部联接user_post up on follow_id = up.user_id

Try this :

试试这个 :

SELECT  u.username,
        u.name,
        u.profile_pic,
        up.* 
FROM    user u,user_post up 
where   up.user_id in 
(SELECT follow_id FROM follow inner join user_post up follow_id=up.user_id) 
AND    description=''
order by postime desc

#2

There's no join condition between user and user_post. If you used JOIN syntax this couldn't happen as the parser will complain about the missing ON.

用户和user_post之间没有连接条件。如果您使用了JOIN语法,则不会发生这种情况,因为解析器会抱怨缺少ON。

(After your comment I added the IN back):

(在您发表评论后我将IN添加回来):

SELECT u.username,u.name,u.profile_pic, up.* 
FROM user u JOIN user_post up 
  ON up.user_id = u.id
WHERE 
   (up.user_id  = $user_id 
    OR
    up.user_id IN (
                     SELECT user_id 
                     FROM follow 
                     WHERE follow_id=$user_id
                   )
   )
AND description='' 
ORDER BY postime DESC

#3

You miss to add the foreign key constraint in where statement

您错过了在where语句中添加外键约束

SELECT u.username,u.name,u.profile_pic, up.* FROM user u, user_post 
up where up.user_id = u.id and up.user_id in (SELECT follow_id FROM follow WHERE follow_id=$user_id) AND description='' order by postime desc

#1