By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
PRIMARY KEY (id)
);
CREATE TABLE roles (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
parent_id INT,
PRIMARY KEY (id)
);
CREATE TABLE user_roles (
id INT NOT NULL AUTO_INCREMENT,
user_id INT,
role_id INT,
PRIMARY KEY (id)
);
INSERT INTO users(name) VALUES ('Alice'),('Bob'),('Charlie');
INSERT INTO roles(name, parent_id) VALUES
('superuser',null),
('admin',1),
('ceo',1),
('employee', null);
INSERT INTO user_roles(user_id, role_id) VALUES
(1,2),
(2,3),
(3,4);
SELECT * FROM users
WHERE EXISTS(
SELECT 1 FROM roles
| id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |