MySQL - 根据团队成员身份返回多个用户

Question

我希望返回所有users，这些成员是请求的用户(通过硬编码user1的uid值来模拟)所属的teams。我正在尝试通过使用连接表teams_members来实现这一点，但最终只能得到查询，而不是返回比我正在寻找的更多的内容。

DB小提琴链接

在查询输出(查询#3)中，我希望看不到user3，因为它们不是team1或team2的一部分，user1是唯一的团队。

设置：

CREATE TABLE users (
uid INT,
name TEXT
);
INSERT INTO users (uid, name) VALUES (1, "user1");
INSERT INTO users (uid, name) VALUES (2, "user2");
INSERT INTO users (uid, name) VALUES (3, "user3");
CREATE TABLE teams (
tuid INT,
name TEXT
);
INSERT INTO teams (tuid, name) VALUES (1, 'team1');
INSERT INTO teams (tuid, name) VALUES (2, 'team2');
INSERT INTO teams (tuid, name) VALUES (3, 'team3');
CREATE TABLE teams_members (
uid INT,
tuid INT,
role TEXT
);
INSERT INTO teams_members (uid, tuid, role) VALUES (1, 1, 'owner');
INSERT INTO teams_members (uid, tuid, role) VALUES (1, 2, 'owner');
INSERT INTO teams_members (uid, tuid, role) VALUES (2, 1, 'owner');
INSERT INTO teams_members (uid, tuid, role) VALUES (3, 3, 'owner');

查询(为上下文添加的其他查询)：

# Get user
SELECT * FROM users WHERE uid = 1;
# Get teams the user is part of
SELECT t.tuid, t.name FROM teams t
LEFT JOIN teams_members tm ON t.tuid = tm.tuid
WHERE uid = 1;
# Get team members present for all teams <<<<<<< Query in question
SELECT u.uid, u.name FROM users u
LEFT JOIN teams_members tm ON u.uid = tm.uid
LEFT JOIN teams t ON tm.tuid = t.tuid
WHERE tm.uid = u.uid;

如何仅返回user1所属团队中存在的users？

Answer 1

您的第二个查询就是您所需要的。使用它从表用户那里获取数据：

select * 
from users where uid in(SELECT t.tuid 
FROM teams t
LEFT JOIN teams_members tm ON t.tuid = tm.tuid
WHERE uid = 1);

这是演示：

演示

Answer 2

如何仅返回 user1 所属团队中的用户？

一种方法使用exists：

select u.*
from users u
where exists (select 1
from teams_members tm1 join
teams_members tm2
on tm1.tuid = tm2.tuid  -- same team
where tm1.uid = 1 and        -- team has user 1
tm2.uid = u.uid        -- team has user in outer query
);