Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users(
   user_id INTEGER  NOT NULL PRIMARY KEY 
  ,name    VARCHAR(8) NOT NULL
);
INSERT INTO users(user_id,name) VALUES (1,'Jerry');
INSERT INTO users(user_id,name) VALUES (2,'George');
INSERT INTO users(user_id,name) VALUES (3,'Elaine');
INSERT INTO users(user_id,name) VALUES (4,'Kramer');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

CREATE TABLE user_roles(
   user_id INTEGER  NOT NULL  
  ,role_id INTEGER  NOT NULL
  ,tree_id INTEGER  NOT NULL
);
INSERT INTO user_roles(user_id,role_id,tree_id) VALUES (1,5,1);
INSERT INTO user_roles(user_id,role_id,tree_id) VALUES (1,5,2);
INSERT INTO user_roles(user_id,role_id,tree_id) VALUES (2,6,1);
INSERT INTO user_roles(user_id,role_id,tree_id) VALUES (3,7,1);
INSERT INTO user_roles(user_id,role_id,tree_id) VALUES (4,8,1);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM user_roles ur
    WHERE ur.user_id = u.user_id AND ur.tree_id in (1, 2, 3)
)


user_id
name


1
Jerry

2
George

3
Elaine

4
Kramer


SELECT u.*
FROM users u
INNER JOIN user_roles ur on u.user_id = ur.user_id
WHERE ur.tree_id in (1, 2, 3)
GROUP BY u.user_id


user_id
name


4
Kramer

3
Elaine

1
Jerry

2
George


SELECT u.*, STRING_AGG(ur.tree_id::text, ',' ORDER BY ur.tree_id) tree_ids
FROM users u
INNER JOIN user_roles ur on u.user_id = ur.user_id
WHERE ur.tree_id in (1, 2, 3)
GROUP BY u.user_id


user_id
name
tree_ids


1
Jerry
1,2

2
George
1

3
Elaine
1

4
Kramer
1