MariaDB 10.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Users (
  id int primary key,
  name varchar(255)
);


insert into Users values 
(1,'admin'),
(2,'user1'),
(3,'user2'),
(4,'user3');

create table OrgUnits (
  id int primary key,
  name varchar(255)
);

insert into OrgUnits values 

(1,'Administration'),
(2,'HR'),
(3,'Payroll'),
(4,'Health & Safety');

create table Users_OrgUnits (
  user_id int,
  orgunit_id int,
  primary key (user_id, orgunit_id)
);

insert into Users_OrgUnits values 
(1,1),
(2,2),
(2,3),
(3,3),
(3,4),
(4,2),
(4,3),
(4,4);
select * from Users;
select * from OrgUnits;
select * from Users_OrgUnits;


id
name


1
admin

2
user1

3
user2

4
user3




id
name


1
Administration

2
HR

3
Payroll

4
Health & Safety




user_id
orgunit_id


1
1

2
2

2
3

3
3

3
4

4
2

4
3

4
4


select u.id,u.name
from Users u 
inner join Users_OrgUnits uou on uou.user_id=u.id
inner join OrgUnits uo on uo.id=uou.orgunit_id
where uo.name in ('Payroll','Health & Safety')
group by u.id,u.name
having count(distinct uo.name) >1;


id
name


3
user2

4
user3


select u.id,u.name
from Users u 
inner join Users_OrgUnits uou on uou.user_id=u.id
inner join OrgUnits uo on uo.id=uou.orgunit_id
where uo.id in (3,4)
group by u.id,u.name
having count(distinct uo.id) >1;


id
name


3
user2

4
user3


select u.id,u.name
from Users u 
inner join Users_OrgUnits uou on uou.user_id=u.id
inner join OrgUnits uo on uo.id=uou.orgunit_id
group by u.id,u.name
having count(distinct orgunit_id) >1;


id
name


2
user1

3
user2

4
user3