MySQL - Return multiple users based on team membership

user_14289294 0
Viewed 105

I am looking to return all users that are members of teams that the requested user (simulated by hard coding the uid value of user1) is part of. I am trying to achieve this by using a joining table teams_members but can only end up with queries than return more than what I am looking for.

DB Fiddle Link

In the query output (Query #3) I am expecting to not see user3 as they are not part of team1 or team2, the only teams that user1 is part of.

Setup:

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

Queries (others added for context):

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

How do I return only users that are present in teams that user1 is part of?

mysql sql
2 Answers

Your second query is all you need. Use it to get the data from table users:

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

Here is the demo:

DEMO

user_65650380

How do I return only users that are present in teams that user1 is part of?

One method uses 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
             );
user_11440350
Related