mysql - 从两个表中获取数据并将它们连接在一起

Question

我有一个名为participant_tb的表，表的列是这样的

participant_id : 1, username: 'atom', fullname: 'abc' participant_id:2, username : 'ion', fullname: 'def' participant_id: 3, username:'molecule', fullname: 'ghi' participant_id: 4, username: 'compound',fullname: 'jkl'

我有另一个表，看起来像这样，该表称为 follower_tb

follow_id: 1, follower_id: 1, followed_id: 2, follow_id: 2,follower_id: 2, followed_id: 1, follow_id: 3, follower_id: 3,followed_id: 1, follow_id: 4, follower_id: 1, followed_id: 4.

一旦有人点击关注按钮，关注者的id将存储在follower_id中，被关注者的id将存储在followed_id列中。我想要做的是:假设“atom”的 participant_id 为 1 已登录，我想获取他关注的人和关注他的人的列表。

我试过这样的:

SELECT p.participant_id, p.username, p.fullname, f.follower_username, f.followed_username, f.follower_id
FROM my_participants p
JOIN my_followers f ON ( p.participant_id = f.followed_id ) 
WHERE followed_id =  1 || follower_id =  1

我遇到的问题是，它只获取那些跟在 atom 后面的。

这个我也试过

SELECT p.participant_id, p.username, p.fullname, f.follower_username, f.followed_username, f.follower_id
FROM my_participants p
JOIN my_followers f ON ( p.participant_id = f.follower_id ) 
WHERE followed_id =  1 || follower_id =  1

我遇到的问题是，它的结果中包含 atom。制作这样的东西

participant_id: 1, username: 'atom', follower_id: 1, followed_id: 2,

participant_id: 2, username: 'ion', follower_id: 1, followed_id: 2,

participant_id: 1, username: 'atom', follower_id: 2, followed_id: 1,participant_id: 2, username: 'ion', follower_id: 2, followed_id: 1,participant_id: 3, username: 'molecule', follower_id: 3, followed_id:1,participant_id: 1, username: 'atom', follower_id: 3, followed_id:1,participant_id: 4, username: 'compound', follower_id: 1,followed_id: 4participant_id: 1, username: 'atoom', follower_id: 1,followed_id: 4

我想要这样的东西:

participant_id: 2, username: 'ion', follower_id: 1, followed_id: 2,participant_id: 2, username: 'ion', follower_id: 2, followed_id: 1,participant_id: 3, username: 'molecule', follower_id: 3, followed_id:1, participant_id: 4, username: 'compound', follower_id: 1,followed_id: 4

或类似这样的内容(第一行显示 atom 正在关注此人，而该人正在关注):

participant_id: 2, username: 'ion', follower_id: 2, followed_id: 2,participant_id: 3, username: 'molecule', follower_id: 3, followed_id:1,participant_id: 4, username: 'compound', follower_id: 1,followed_id: 4

Answer 1

这看起来像是一份 SQL Subquery 的工作，它允许您将一个查询的结果用作另一个查询的输入。事实证明，获取关注者和关注者的查询非常相似。

#select those FOLLOWING the logged-in account

SELECT p.participant_id, p.username, f.follower_id, f.followed_id FROM participant_tb AS p
JOIN follower_tb AS f ON (p.participant_id = f.follower_id)
WHERE p.participant_id IN (
    #make a sub-query to fetch the follower_id's of the entries that follow participant_id 1

    SELECT f.follower_id
    FROM participant_tb AS p
    JOIN follower_tb AS f ON (p.participant_id = f.followed_id)
    WHERE participant_id = 1 #your value here
);

子查询挑选出每个 participant_id 1 之后的 follower_id，然后使用该 ID 列表从 participant_tb 中挑选用户名.

#select those BEING FOLLOWED BY the logged-in account

SELECT p.participant_id, p.username, f.follower_id, f.followed_id FROM participant_tb AS p
JOIN follower_tb AS f ON (p.participant_id = f.followed_id)
WHERE p.participant_id IN (
    #make a sub-query to fetch the follower_id's of the entries that are being followed by participant_id 1

    SELECT f.followed_id
    FROM participant_tb AS p
    JOIN follower_tb AS f ON (p.participant_id = f.follower_id)
    WHERE participant_id = 1 #your value here
);

此子查询几乎相同，但它选择了 participant_id 1 后跟的所有元素的 followed_id。

查询1的返回值:

particiapnt_id  username    follower_id followed_id
2               ion         2           1
3               molecule    3           1

查询2的返回值:

particiapnt_id  username    follower_id followed_id
2               ion         1           2
4               compound    1           4