There is two tables:

dependency_permission table:

user_permission table:

I want to write a query that finds all user_id's which have permissions depend on another permission the user doesn't have. from the above data, users (22222 and 33333) should be returned, they don't have permission 1 which 2 depends on.

CodePudding user response：

You may left join the 'user_permission' table with the 'dependency_permission' to get the 'dependency_permission_id' for each user 'permission_id', then use NOT EXISTS operator to check the existence of 'dependency_permission_id' for each user 'permission_id'.

with user_dependency_permission as
(
  select U.user_id, U.permission_id, D.dependency_permission_id
  from user_permission U left join dependency_permission D
  on U.permission_id = D.id 
)
select user_id, permission_id /* if you want to select only user_ids use distinct user_id*/
from user_dependency_permission T
where not exists(
                  select 1 from user_dependency_permission D
                  where T.user_id=D.user_id and 
                  D.permission_id=T.dependency_permission_id
                )
and T.dependency_permission_id is not null

See a demo on MySQL.