mysql - 从两个不同的表中检索的 SQL 连接问题

Question

我的 sql 中有以下 3 个表

1. person table

user_id active  email           fullName        joined              password
bucky   1       bucky@abc.com   Bucky Wall      1/30/2017 12:43:46  bucky
james   1       james@abc.com   James Goedic    1/30/2017 12:43:19  james
praveen 1       praveen@abc.com Praveen Reddy S 1/30/2017 12:37:40  praveen
priya   1       priya@abc.com   Priya Reddy     1/10/2017 10:30:17  priya

2. tweet table

tweet_id    created             message                         user_id
 1        1/30/2017 12:45:37    @Bucky!. Meet me in office      james
 2        1/30/2017 12:45:58    @James!. How nare you?          praveen
 3        1/30/2017 12:46:10    Hi, Every One                   praveen
 4        1/30/2017 12:46:38    @James: Hellow James            bucky
 5        1/31/2017 10:30:50    This is Priya                   priya


 3. following table

 user_id        following_id
 praveen         bucky
 praveen        james

现在我想检索用户 praveen 及其指定的关注用户 bucky 和 ​​james 的消息。 praveen 用户有两条消息，bucky 有一条消息，james 有一条消息。我要显示的总共 4 条消息。

我尝试了流动查询但它不起作用。帮助我得到正确的查询

SELECT 
    *
FROM
    tweet t
WHERE
    t.user_id = (SELECT 
            f.user_id
        FROM
            following f
        WHERE
            f.following_id = f.user_id)
        OR t.user_id = 'praveen'

Answer 1

你应该使用in来比较多个userID并且你想关注人名，所以你应该选择following_id来获取它的数据。在 where 条件下你应该写 user_id='praveen'

  SELECT 
        *
    FROM
        tweet t
    WHERE
        t.user_id in (SELECT 
                f.following_id
            FROM
                following f
            WHERE
                f.user_id ='praveen')
            OR t.user_id = 'praveen'