Tyson Tyson - 3 days ago 7
SQL Question

SQL: Selecting rows whose distinct values are a subset of another table's distinct values

Given the following tables:

plan_role

id | plan_id | role_id
----+---------+---------
7 | 1 | 10
8 | 1 | 20
9 | 1 | 30
10 | 2 | 10
11 | 2 | 20
12 | 3 | 30
13 | 4 | 40

user_role

id | user_id | role_id
----+---------+---------
8 | 100 | 10
9 | 100 | 20
10 | 100 | 40
11 | 200 | 10
12 | 200 | 20
13 | 200 | 30
14 | 200 | 40


What's the SQL query that will return all plan_ids such that a given user_id's set of role_ids is a superset of the set of role_ids for the plan_ids?

In other words, how do I find plans for a given user such that the user shares at least all roles with each plan.

For example,
user_id
100 should return
plan_id
s 2, 4 and
user_id
200 should return 1, 2, 3, 4.

For convenience, here's the setup I'm using:

CREATE TABLE plan_role(
id serial PRIMARY KEY,
plan_id integer NOT NULL,
role_id integer NOT NULL
);

CREATE TABLE user_role(
id serial PRIMARY KEY,
user_id integer NOT NULL,
role_id integer NOT NULL
);

INSERT INTO plan_role (plan_id, role_id) VALUES (1, 10);
INSERT INTO plan_role (plan_id, role_id) VALUES (1, 20);
INSERT INTO plan_role (plan_id, role_id) VALUES (1, 30);
INSERT INTO plan_role (plan_id, role_id) VALUES (2, 10);
INSERT INTO plan_role (plan_id, role_id) VALUES (2, 20);
INSERT INTO plan_role (plan_id, role_id) VALUES (3, 30);
INSERT INTO plan_role (plan_id, role_id) VALUES (4, 40);

INSERT INTO user_role (user_id, role_id) VALUES (100, 10);
INSERT INTO user_role (user_id, role_id) VALUES (100, 20);
INSERT INTO user_role (user_id, role_id) VALUES (100, 40);
INSERT INTO user_role (user_id, role_id) VALUES (200, 10);
INSERT INTO user_role (user_id, role_id) VALUES (200, 20);
INSERT INTO user_role (user_id, role_id) VALUES (200, 30);
INSERT INTO user_role (user_id, role_id) VALUES (200, 40);

Answer

I would just use a LEFT JOIN here with a rollup by plan to check if each role belong to a certain user.

SELECT t1.plan_id
FROM rms.plan_role t1
LEFT JOIN rms.user_role t2
    ON t1.role_id = t2.role_id AND
       t2.user_id = 100
GROUP BY t1.plan_id
HAVING SUM(CASE WHEN t2.id IS NULL THEN 1 ELSE 0 END) = 0

Demo here:

SQLFiddle

(demo shown in MySQL, though the syntax is almost identical in Postgres except possibly for table names)

Comments