Find records with join matching all join IDs

All we need is an easy explanation of the problem, so here it is.

Suppose to have the following database setup:

Users (
  id int primary key,
  name varchar(255)
)

OrgUnits (
  id int primary key,
  name varchar(255)
)

Users_OrgUnits (
  user_id int,
  orgunit_id int,
  primary key (user_id, orgunit_id)
)

You could easily find all users belonging to a certain OU with a query like

SELECT U.*
FROM Users U
JOIN Users_OrgUnits UOU ON (
  U.user_id = UOU.user_id
  AND UOU.orgunit_id = X
)

Is there a way, with a single query, to ask the database for all users belonging to two or more OUs at the same time?

If you do something like

SELECT U.*
FROM Users U
JOIN Users_OrgUnits UOU ON (
  U.user_id = UOU.user_id
  AND UOU.orgunit_id IN (X, Y)
)

You’d get users belonging to at least one of those UOs, not necessarily both of them.

EXAMPLE:

Users

id name
1 admin
2 user1
3 user2
4 user3

OrgUnits

id name
1 Administration
2 HR
3 Payroll
4 Health & Safety

Users_OrgUnits

user_id orgunit_id
1 1
2 2
2 3
3 3
3 4
4 2
4 3
4 4

Now, I would like to ask for all users belonging to both "Payroll" and "Health & Safety", that is, OUs with ID 3 and 4, and get this result:

id name
3 user2
4 user3

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This can be done by a simple having count clause:

I would like to ask for all users belonging to both "Payroll" and
"Health & Safety", that is, OUs with ID 3 and 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;

or written differently using the id

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;.

Is there a way, with a single query, to ask the database for all users
belonging to two or more OUs at the same time?

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;

https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=e1d7a2db24595a21c3b18f08f7f0adb6

having count(distinct orgunit_id) >1; will return only the values repeated more than once 2,3,… for each user_id on Users_OrgUnits table

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply