SQL: select rows whose individual values ​​are a subset of another table.

Question

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 is an SQL query that will return all plan_ids objects, so this set of users_users is a superset of the role_ids set for plan_ids?

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

For example, user_id100 should return plan_id2, 4, and user_id200 should return 1, 2, 3, 4.

For convenience, the setting I use here is used:

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 1

LEFT JOIN , , .

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

:

SQLFiddle

( MySQL, Postgres, , , )

Answer 2

, , .

select user_plans.* from 
(
    -- all users with all plans
    select distinct user_id, plan_id from user_role, plan_role
) user_plans
left join 
(
    select user_plans_roles.plan_id, user_plans_roles.user_id from (
        -- get the result assuming user has all plans and roles
        select distinct plan_id, user_id, plan_role.role_id 
        from plan_role, user_role
    ) user_plans_roles
    left join user_role users
    on user_plans_roles.user_id = users.user_id
    and user_plans_roles.role_id = users.role_id
    -- exclude users without a matching role_id
    where users.user_id is null
) missing_user_plan_roles
on user_plans.user_id = missing_user_plan_roles.user_id
and user_plans.plan_id = missing_user_plan_roles.plan_id
-- exclude missing roles 
where missing_user_plan_roles.user_id is null
order by user_id, plan_id

-

user_id plan_id
100 2
100 4
200 1
200 2
200 3
200 4

SQL,

select user_plans_roles.plan_id, user_plans_roles.user_id from (
    -- get the result assuming user has all plans and roles
    select distinct plan_id, user_id, plan_role.role_id 
    from plan_role, user_role
) user_plans_roles
left join user_role users
on user_plans_roles.user_id = users.user_id
and user_plans_roles.role_id = users.role_id
-- exclude users without a matching role_id
where users.user_id is null

plan_id user_id
1   100
3   100

Answer 3

exists in:

select distinct pr.plan_id
from plan_role pr
where pr.role_id in (select ur.role_id
                     from user_role ur
                     where ur.user_id = 200
                    );

, , 200.