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

Question

我想退货users是 teams 的成员请求的用户(通过硬编码 uid 的 user1 值模拟)是其中的一部分。我试图通过使用连接表来实现这一点 teams_members但最终只能得到查询而不是返回比我正在寻找的更多。
DB Fiddle Link
在查询输出(查询 #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;

我如何退货 只有 users存在于 user1 的团队中是其一部分？

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);

这是演示:

DEMO