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);
Records: 3 Duplicates: 0 Warnings: 0
Records: 4 Duplicates: 0 Warnings: 0
Records: 3 Duplicates: 0 Warnings: 0
WITH RECURSIVE cte AS (
-- The role and its descendants
SELECT *
FROM roles
WHERE name = 'superuser'
UNION ALL
SELECT roles.*
FROM roles
JOIN cte ON cte.id = roles.parent_id
)
SELECT DISTINCT u.*
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN cte r ON r.id = ur.role_id
| id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |